Excel 的导入与导出是后台最常用的功能之一了,这里分享一下实现方式。
导入
要实现 excel 导入,这里使用 OLEDB,先引用 System.Data.OleDb。
- public DataTable InputExcel(string TableName) {
- try {
- string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
- OleDbConnection conn = new OleDbConnection(strConn);
- conn.Open();
- string strExcel = "select * from [" + TableName + "$]";
- OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
- DataTable dt = new DataTable();
- myCommand.Fill(dt);
- conn.Close();
- return dt;
- } catch(Exception ex) {
- return null;
- }
- }
上面的函数以 Sheet 名作为输入,支持中文,可以选择 Excel 里面的哪张表来导入,读取的数据保存在 DataTable。然后就可以对 DataTable 里面的数据逐行处理并插入数据库。
导出
如果数据是存在 GridView 控件里面的,那么有个简便方法可以导出,先添加 System.web.UI 和 System.Web.UI.htmlControls 引用。
- public void gridviewtoexcel(GridView gv, string filename) {
- StringBuilder sb = new StringBuilder();
- StringWriter sw = new StringWriter(sb);
- HtmlTextWriter htw = new HtmlTextWriter(sw);
- Page page = new Page();
- HtmlForm form = new HtmlForm();
- for (int i = 0; i < gv.Rows.Count; i++) {
- if (gv.Rows[i].FindControl("GridView2") != null) {
- gv.Rows[i].Cells.RemoveAt(0);
- gv.Rows[i].Cells.RemoveAt(gv.Rows[i].Cells.Count - 1);
- }
- }
- gv.EnableViewState = false; // Deshabilitar la validación de eventos, sólo asp.net 2page.EnableEventValidation = false;// Realiza las inicializaciones de la instancia de la clase Page que requieran los dise?adores RAD.page.DesignerInitialize();page.Controls.Add(form);form.Controls.Add(gv);page.RenderControl(htw);System.Web.HttpContext.Current.Response.Clear();System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");System.Web.HttpContext.Current.Response.Charset = "UTF-8";System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.Default;System.Web.HttpContext.Current.Response.ContentType = "application/excel";string style = @"
上面是实现导出的标准代码,其中 Charset 和 style 的设置用来支持中文和避免乱码。
但如果是 MVC 的话这个就不太合适了,而且这种导出方式也有点问题,当遇到大且复杂的 gridview 时就知道了。那么这时候就用下面的方法,在使用前需要引用 NPOI.XSSF.UserModel,NPOI.SS.UserModel,NPOI.SS.Util。
- ////// 根据dataTable生成Excel//////数据源///sheet名称///标题///public static XSSFWorkbook Export(DataTable dt, string sheetName, string header, out string msg){try{XSSFWorkbook workbook = new XSSFWorkbook();ISheet sheet;if (!string.IsNullOrEmpty(sheetName))sheet = workbook.CreateSheet(sheetName);elsesheet = workbook.CreateSheet("sheet1");ICellStyle dateStyle = workbook.CreateCellStyle();IDataFormat format = workbook.CreateDataFormat();dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd hh:ss:mm");//取得列宽int[] arrColWidth = new int[dt.Columns.Count];foreach (DataColumn item in dt.Columns){arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;}for (int i = 0; i < dt.Rows.Count; i++){for (int j = 0; j < dt.Columns.Count; j++){int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;if (intTemp > arrColWidth[j]){arrColWidth[j] = intTemp;}}}int rowIndex = 0;foreach (DataRow row in dt.Rows){#region 新建表,填充表头,填充列头,样式//if (rowIndex >= 1048570 || rowIndex == 0)if (rowIndex >= 1048570 || rowIndex == 0){if (rowIndex != 0){sheet = workbook.CreateSheet();}#region 表头及样式{IRow headerRow = sheet.CreateRow(0);headerRow.HeightInPoints = 25;headerRow.CreateCell(0).SetCellValue(header);ICellStyle headStyle = workbook.CreateCellStyle();headStyle.Alignment = HorizontalAlignment.Center;IFont font = workbook.CreateFont();font.FontHeightInPoints = 20;font.Boldweight = 700;headStyle.SetFont(font);headerRow.GetCell(0).CellStyle = headStyle;sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));//headerRow}#endregion#region 列头及样式{IRow headerRow = sheet.CreateRow(1);ICellStyle headStyle = workbook.CreateCellStyle();headStyle.Alignment = HorizontalAlignment.Center;IFont font = workbook.CreateFont();font.FontHeightInPoints = 10;font.Boldweight = 700;headStyle.SetFont(font);foreach (DataColumn column in dt.Columns){headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);headerRow.GetCell(column.Ordinal).CellStyle = headStyle;//设置列宽sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);}//headerRow.Dispose();}#endregionrowIndex = 2;}#endregion#region 填充内容IRow dataRow = sheet.CreateRow(rowIndex);foreach (DataColumn column in dt.Columns){ICell newCell = dataRow.CreateCell(column.Ordinal);string drValue = row[column].ToString();switch (column.DataType.ToString()){case "System.String"://字符串类型newCell.SetCellValue(drValue);break;case "System.DateTime"://日期类型DateTime dateV;bool torf = DateTime.TryParse(drValue, out dateV);if (torf && dateV != System.Data.SqlTypes.SqlDateTime.MinValue && dateV != new DateTime()){newCell.SetCellValue(dateV);newCell.CellStyle = dateStyle;//格式化显示}elsenewCell.SetCellValue("");break;case "System.Boolean"://布尔型bool boolV = false;bool.TryParse(drValue, out boolV);newCell.SetCellValue(boolV);break;case "System.Int16"://整型case "System.Int32":case "System.Int64":case "System.Byte":int intV = 0;int.TryParse(drValue, out intV);newCell.SetCellValue(intV);break;case "System.Decimal"://浮点型case "System.Double":double doubV = 0;double.TryParse(drValue, out doubV);newCell.SetCellValue(doubV);break;case "System.DBNull"://空值处理newCell.SetCellValue("");break;default:newCell.SetCellValue("");break;}}#endregionrowIndex++;}msg = string.Empty;return workbook;}catch (Exception ex){msg = ex.Message;return null;}//try//{// FileStream fs = new FileStream(filename, FileMode.CreateNew);//(filename, FileMode.Create, FileAccess.Write);// workbook.Write(fs);// //fs.Flush();// return true;//}//catch (Exception ex)//{// throw new Exception(ex.Message);// return false;//}//using (MemoryStream ms = new MemoryStream())//{// workbook.Write(ms);// ms.Flush();// //ms.Position = 0;// //sheet.Dispose();// return ms;//}}public void DatatableToExcel(DataTable list){string msg = string.Empty;XSSFWorkbook work = Export(list, "", "header", out msg);Response.Clear();Response.ClearHeaders();if (work == null && !string.IsNullOrEmpty(msg))throw new Exception(msg);Response.Buffer = false;Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";//ContentType;Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("export.xlsx", System.Text.Encoding.UTF8));MemoryStream ms = new MemoryStream();work.Write(ms);Response.BinaryWrite(ms.ToArray());work = null;ms.Close();ms.Dispose();Response.Flush();Response.End();}
上面这种导出 Excel 方式同样适用于 MVC,可以实现 DataTable 到 Excel 的导出,并且网页端直接弹出文件下载。如果要保存文件到本地可以参考注释掉的那段代码。
本教程代码参考
EshineASPNet\Web\App_Code\upExcel.cs
EshineASPNet\Web\App_Code\PublicClass.cs
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: