首先需要一個Column的類，代表一列，還需要一個Sheet類，代表一個Sheet頁。
public class Column { public string Code { get; set; } public string Name { get; set; } public string DataType { get; set; } public int Width { get; set; } public bool Hidden { get; set; } public Column() { } public Column(string code, string name, string dataType, int width, bool hidden = false) { Code = code; Name = name; DataType = dataType; Width = width; Hidden = hidden; } } public class Sheet { public string Name { get; set; } public List<Column> Columns { get; set; } public DataTable DataSource { get; set; } public Sheet() { } public Sheet(string name, List<Column> columns, DataTable dataSource) { Name = name; Columns = columns; DataSource = dataSource; } }
封裝一個Workbook，方便操作。
1 /// <summary> 2 /// 工作薄 3 /// </summary> 4 public class Workbook 5 { 6 public HSSFWorkbook workbook; 7 /// <summary> 8 /// 表頭格式 9 /// </summary> 10 PRivate HSSFCellStyle HeadStyle 11 { 12 get 13 { 14 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 15 headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; 16 headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; 17 HSSFFont font = (HSSFFont)workbook.CreateFont(); 18 font.FontHeightInPoints = 10; 19 font.Boldweight = 700; 20 headStyle.SetFont(font); 21 return headStyle; 22 } 23 } 24 /// <summary> 25 /// 時間格式 26 /// </summary> 27 private HSSFCellStyle DateStyle 28 { 29 get 30 { 31 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 32 HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); 33 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); 34 return dateStyle; 35 } 36 } 37 38 /// <summary> 39 /// 實例一個工作薄 40 /// </summary> 41 public Workbook() 42 { 43 workbook = new HSSFWorkbook(); 44 #region 右擊文件 屬性信息 45 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 46 dsi.Company = "SiBu"; 47 workbook.DocumentSummaryInformation = dsi; 48 49 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 50 si.CreateDateTime = System.DateTime.Now; 51 workbook.SummaryInformation = si; 52 #endregion 53 } 54 55 /// <summary> 56 /// 加載Excel文件 57 /// </summary> 58 /// <param name="filePath">文件路徑</param> 59 public Workbook(string filePath) 60 { 61 using (FileStream file = new FileStream(filePath, FileMode.Open, Fileaccess.Read)) 62 { 63 workbook = new HSSFWorkbook(file); 64 } 65 } 66 67 /// <summary> 68 /// 獲取Sheet頁的數據 69 /// </summary> 70 /// <param name="sheetIndex">Sheet頁Index，從0開始</param> 71 /// <returns>DataTable</returns> 72 public DataTable GetDataTable(int sheetIndex = 0) 73 { 74 DataTable dt = new DataTable(); 75 76 HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex); 77 System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 78 79 HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); 80 int cellCount = headerRow.LastCellNum; 81 82 for (int j = 0; j < cellCount; j++) 83 { 84 HSSFCell cell = (HSSFCell)headerRow.GetCell(j); 85 dt.Columns.Add(cell.ToString()); 86 } 87 88 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 89 { 90 HSSFRow row = (HSSFRow)sheet.GetRow(i); 91 if (row == null) 92 continue; 93 DataRow dataRow = dt.NewRow(); 94 95 for (int j = row.FirstCellNum; j < cellCount; j++) 96 { 97 ICell cell = row.GetCell(j); 98 if (cell != null) 99 {100 if (cell.CellType == CellType.Numeric)101 {102 //NPOI中數字和日期都是NUMERIC類型的，這里對其進行判斷是否是日期類型103 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期類型104 {105 dataRow[j] = cell.DateCellValue;106 }107 else//其他數字類型108 {109 dataRow[j] = cell.NumericCellValue;110 }111 }112 else if (cell.CellType == CellType.Blank)//空數據類型113 {114 dataRow[j] = "";115 }116 else if (cell.CellType == CellType.Formula)//公式類型117 {118 HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);119 dataRow[j] = eva.Evaluate(cell).StringValue;120 }121 else //其他類型都按字符串類型來處理122 {123 dataRow[j] = cell.StringCellValue;124 }125 }126 }127 128 dt.Rows.Add(dataRow);129 }130 return dt;131 }132 133 /// <summary>134 /// 創建一個Sheet頁135 /// </summary>136 /// <param name="Sheet">Sheet</param>137 public void CreateSheet(Sheet sheetInfo)138 {139 if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook.NumberOfSheets + 1;140 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetInfo.Name);141 142 int rowIndex = 0;143 144 #region 新建表，填充表頭，填充列頭，樣式145 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);146 headerRow.HeightInPoints = 20;147 var columIndex = 0;148 foreach (var column in sheetInfo.Columns)149 {150 headerRow.CreateCell(columIndex).SetCellValue(column.Name);151 headerRow.GetCell(columIndex).CellStyle = HeadStyle;152 //設置列寬153 sheet.SetColumnWidth(columIndex, column.Width * 256);154 sheet.SetColumnHidden(columIndex, column.Hidden);155 columIndex++;156 }157 158 #endregion159 #region 填充內容160 rowIndex = 1;161 foreach (DataRow row in sheetInfo.DataSource.Rows)162 {163 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);164 var columnIndex = 0;165 foreach (var column in sheetInfo.Columns)166 {167 HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);168 if (!sheetInfo.DataSource.Columns.Contains(column.Code))169 {170 newCell.SetCellValue("");171 }172 else173 {174 string drValue = row[column.Code].ToString();175 176 switch (column.DataType.ToUpper())177 {178 case "S"://字符串類型179 newCell.SetCellValue(drValue);180 break;181 case "D"://日期類型182 System.DateTime dateV;183 System.DateTime.TryParse(drValue, out dateV);184 newCell.SetCellValue(dateV);185 newCell.CellStyle = DateStyle;//格式化顯示186 break;187 case "B"://布爾型188 bool boolV = false;189 bool.TryParse(drValue, out boolV);190 newCell.SetCellValue(boolV);191 break;192 case "I"://整型193 int intV = 0;194 int.TryParse(drValue, out intV);195 newCell.SetCellValue(intV);196 break;197 case "F"://浮點型198 double doubV = 0;199 double.TryParse(drValue, out doubV);200 newCell.SetCellValue(doubV);201 break;202 default:203 newCell.SetCellValue(drValue);204 break;205 }206 }207 columnIndex++;208 }209 rowIndex++;210 }211 #endregion212 }213 214 /// <summary>215 /// 保存216 /// </summary>217 /// <param name="filePath">文件路徑</param>218 public void SaveAs(string filePath)219 {220 using (MemoryStream ms = new MemoryStream())221 {222 workbook.Write(ms);223 ms.Flush();224 ms.Position = 0;225 226 using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))227 {228 byte[] data = ms.ToArray();229 fs.Write(data, 0, data.Length);230 fs.Flush();231 }232 }233 }234 235 /// <summary>236 /// 獲取Workbook的MemoryStream237 /// </summary>238 /// <returns></returns>239 public MemoryStream GetMemoryStream()240 {241 MemoryStream ms = new MemoryStream();242 workbook.Write(ms);243 ms.Flush();244 ms.Position = 0;245 return ms;246 }247 }
ExcelController接收客戶端Post過來的數據，處理后返回文件流。
public class ExcelController : Controller { [HttpPost] public FileResult CommonExport(string Title, string Columns, string Data) { var tb = JsonConvert.DeserializeObject<DataTable>(Data); var Columnslist = JsonConvert.DeserializeObject<List<Column>>(Columns); var workbook = new Workbook(); workbook.CreateSheet(new Sheet(Title, Columnslist, tb)); var fileStream = workbook.GetMemoryStream(); return File(fileStream, "application/ms-excel", string.Format("{0}.xls", Title)); } }
JS處理數據后POST到后臺。這里面用到了linq.js，通過構造Form表單提交，直接用Jquery的Post獲取到文件流沒反應。
這里的用到了EasyUI的datagrid,可以封裝成母版頁(OSharp里面有介紹 http://www.survivalescaperooms.com/guomingfeng/p/osharp-easyui-Opera.html)，導出Excel方法直接寫在這里面。
function exportToExcel() { $("#exportToExcelForm").remove(); var form = $("<form>");//定義一個form表單 form.attr("id", "exportToExcelForm"); form.attr("style", "display:none"); form.attr("target", ""); form.attr("method", "post"); form.attr("action", "/Excel/CommonExport"); var input1 = $("<input>"); input1.attr("type", "hidden"); input1.attr("name", "Title"); input1.attr("value", '@ViewBag.Title'); var input2 = $("<input>"); input2.attr("type", "hidden"); input2.attr("name", "Columns"); input2.attr("value", JSON.stringify(getColumns())); var input3 = $("<input>"); input3.attr("type", "hidden"); input3.attr("name", "Data"); input3.attr("value", JSON.stringify(getData(grid.datagrid("getRows")))); $("body").append(form);//將表單放置在web中 form.append(input1); form.append(input2); form.append(input3); form.submit();//表單提交 $("#exportToExcelForm").remove(); } function getData(Data) { return Enumerable.From(Data).Select(function (c) { var obj = {}; for (var i in columns[0]) { obj[columns[0][i].field] = c[columns[0][i].field]; } return obj }).ToArray(); } function getColumns() { return Enumerable.From(columns[0]).Select(function (c) { var obj = {}; obj.Code = c.field; obj.Name = c.title; obj.DataType = c.datatype || "S"; obj.Width = (c.width || 80) / 10; obj.Hidden = c.hidden; return obj }).ToArray(); }
新聞熱點
疑難解答
