MVC execl 导入导出
<input id="xls" name="xls" type="file" style="210px;" /> <input type="submit" value="导入" /> <a href="~/Demo/Excel_ExcelExport">导出</a> #region Excel public ActionResult Excel_Index() { return View(); } [HttpPost] public ActionResult Excel_Index(FormCollection formdata) { ViewBag.Data = null; ViewBag.Msg = string.Empty; if (this.HttpContext.Request.Files.Count <= 0 || this.HttpContext.Request.Files[0].ContentLength <= 0) { ViewBag.Msg = "请选择文件！"; return View(); } string fileExt = System.IO.Path.GetExtension(this.HttpContext.Request.Files[0].FileName).ToLower(); if (fileExt != ".xls" && fileExt != ".xlsx") { ViewBag.Msg = "选择的文件格式不对！"; return View(); } ViewBag.Data = Demo.ImportFromStream(this.HttpContext.Request.Files[0].InputStream); return View(); } public ActionResult Excel_ExcelExport() { DataTable dt = new DataTable(); DataColumn dc1 = new DataColumn("column1", Type.GetType("System.String")); DataColumn dc2 = new DataColumn("column2", Type.GetType("System.String")); DataColumn dc3 = new DataColumn("column3", Type.GetType("System.String")); DataColumn dc4 = new DataColumn("column4", Type.GetType("System.String")); DataColumn dc5 = new DataColumn("column5", Type.GetType("System.String")); dt.Columns.Add(dc1); dt.Columns.Add(dc2); dt.Columns.Add(dc3); dt.Columns.Add(dc4); dt.Columns.Add(dc5); for (int i = 0; i < 10; i++) { DataRow dr = dt.NewRow(); dr["column1"] = "test1"; dr["column2"] = "test2"; dr["column3"] = "test3"; dr["column4"] = "test4"; dr["column5"] = "test5"; dt.Rows.Add(dr); } byte[] bytes = Demo.ExportToBytes(dt); return File(bytes, "application/x-excel", "ExcelExport.xls"); } #endregion
NPOI插件
using NPOI.SS.UserModel; /// <summary> /// Excel导入导出工具类 /// </summary> public static class Demo { /// <summary> /// 导入Excel /// </summary> public static System.Data.DataTable ImportFromStream(System.IO.Stream fileStream) { NPOI.SS.UserModel.IWorkbook workbook; NPOI.SS.UserModel.ISheet sheet; System.Data.DataTable data = new System.Data.DataTable(); try { try { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream); //07 } catch { workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); //03 } //第一个sheet工作表 sheet = workbook.GetSheetAt(0); if (sheet != null) { //第一行 IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { System.Data.DataColumn column = new System.Data.DataColumn(firstRow.GetCell(i).StringCellValue); data.Columns.Add(column); } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = sheet.FirstRowNum; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null System.Data.DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null // dataRow[j] = row.GetCell(j).ToString(); if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null { switch (row.GetCell(j).CellType) { case CellType.String: //文本 dataRow[j] = row.GetCell(j).StringCellValue; break; case CellType.Numeric: //数值 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: //bool dataRow[j] = row.GetCell(j).BooleanCellValue; break; case CellType.Blank: //空白 dataRow[j] = ""; break; default: dataRow[j] = "ERROR"; break; } } } data.Rows.Add(dataRow); } } return data; } catch (Exception) { return null; } } /// <summary> /// 导入Excel /// </summary> public static System.Data.DataTable ImportFromBytes(byte[] fileBytes) { System.Data.DataTable table = null; if (fileBytes == null || fileBytes.Length <= 0) { return table; } using (var fileStream = new System.IO.MemoryStream(fileBytes)) { table = ImportFromStream(fileStream); } return table; } /// <summary> /// 导入Excel /// </summary> public static System.Data.DataTable ImportFromFilename(string fileName) { System.Data.DataTable table = null; if (!System.IO.File.Exists(fileName)) { return table; } using (var fileStream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read)) { table = ImportFromStream(fileStream); } return table; } /// <summary> /// 导出Excel /// </summary> public static System.IO.Stream ExportToStream(System.Data.DataTable table) { System.IO.MemoryStream ms = new System.IO.MemoryStream(); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); IRow headRow = sheet.CreateRow(0); foreach (System.Data.DataColumn column in table.Columns) { headRow.CreateCell(column.Ordinal).SetCellValue(string.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption); } int rowIndex = 1; foreach (System.Data.DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (System.Data.DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); sheet = null; headerRow = null; workbook = null; return ms; } /// <summary> /// 导出Excel /// </summary> public static byte[] ExportToBytes(System.Data.DataTable table) { byte[] bytes = null; using (var stream = ExportToStream(table)) { bytes = new byte[stream.Length]; stream.Read(bytes, 0, bytes.Length); stream.Seek(0, System.IO.SeekOrigin.Begin); } return bytes; } }
Datatable导出Excel
//Datatable导出Excel private static void GridToExcelByNPOI(DataTable dt, string strExcelFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { ILog log = LogManager.GetLogger("Exception Log"); log.Error(ex.Message + Environment.NewLine + ex.StackTrace); //记录AuditTrail CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex); MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { workbook = null; } }
Excel文件导成Datatable
/// <summary> /// Excel文件导成Datatable /// </summary> /// <param name="strFilePath">Excel文件目录地址</param> /// <param name="strTableName">Datatable表名</param> /// <param name="iSheetIndex">Excel sheet index</param> /// <returns></returns> public static DataTable XlSToDataTable(string strFilePath, string strTableName,int iSheetIndex) { string strExtName = Path.GetExtension(strFilePath); DataTable dt = new DataTable(); if (!string.IsNullOrEmpty(strTableName)) { dt.TableName = strTableName; } if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx")) { using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read)) { HSSFWorkbook workbook = new HSSFWorkbook(file); ISheet sheet = workbook.GetSheetAt(iSheetIndex); //列头 foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells) { dt.Columns.Add(item.ToString(),typeof(string)); } //写入内容 System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); while(rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; if (row.RowNum == sheet.FirstRowNum) { continue; } DataRow dr = dt.NewRow(); foreach (ICell item in row.Cells) { switch (item.CellType) { case CellType.Boolean: dr[item.ColumnIndex] = item.BooleanCellValue; break; case CellType.Error: dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue); break; case CellType.Formula: switch (item.CachedFormulaResultType) { case CellType.Boolean: dr[item.ColumnIndex] = item.BooleanCellValue; break; case CellType.Error: dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue); break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(item)) { dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss"); } else { dr[item.ColumnIndex] = item.NumericCellValue; } break; case CellType.String: string str = item.StringCellValue; if (!string.IsNullOrEmpty(str)) { dr[item.ColumnIndex] = str.ToString(); } else { dr[item.ColumnIndex] = null; } break; case CellType.Unknown: case CellType.Blank: default: dr[item.ColumnIndex] = string.Empty; break; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(item)) { dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss"); } else { dr[item.ColumnIndex] = item.NumericCellValue; } break; case CellType.String: string strValue = item.StringCellValue; if (string.IsNullOrEmpty(strValue)) { dr[item.ColumnIndex] = strValue.ToString(); } else { dr[item.ColumnIndex] = null; } break; case CellType.Unknown: case CellType.Blank: default: dr[item.ColumnIndex] = string.Empty; break; } } dt.Rows.Add(dr); } } } return dt; }