首页 > 代码库 > 导出列表信息至Excel
导出列表信息至Excel
导出列表信息，可通过使用模板导出，也可直接导出。
一、直接导出列表信息
/// <summary> /// 导出excel文件 /// </summary> /// <param name="reportTitle">标题</param> /// <param name="dt">数据源</param> /// <param name="columns">列名</param> /// <param name="filePath">文件路径</param> /// <param name="maxRow">一页显示最大记录数</param> public static void Export(string reportTitle, List<string> subTitles, DataTable dt, Dictionary<string, string> columns, string filePath, int maxRow) { int WorkSheetCount = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / maxRow)); XlsDocument doc = new XlsDocument(); for (int i = 0; i < WorkSheetCount; i++) { Worksheet sheet = doc.Workbook.Worksheets.Add(string.Format("Sheet{0}", i + 1)); int rowIndex = 0; int colIndex = 0; int currentMinRowIndex = i * maxRow; int currentMaxRowIndex = 0; if (((i + 1) * maxRow + 1) < dt.Rows.Count) currentMaxRowIndex = (i + 1) * maxRow; else currentMaxRowIndex = dt.Rows.Count; //标题 if (!string.IsNullOrEmpty(reportTitle)) { rowIndex++; XF cellXF = doc.NewXF(); cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.HorizontalAlignment = HorizontalAlignments.Centered; cellXF.Font.Height = 24 * 12; cellXF.Font.Bold = true;
sheet.Cells.Add(rowIndex, 1, reportTitle, cellXF); MergeArea area = new MergeArea(rowIndex, rowIndex, 1, columns.Count); sheet.AddMergeArea(area); } //副本标题 if (subTitles != null && subTitles.Count > 0) { foreach (string sub in subTitles) { rowIndex++; sheet.Cells.Add(rowIndex, 1, sub); MergeArea area = new MergeArea(rowIndex, rowIndex, 1, columns.Count); sheet.AddMergeArea(area); } } //列头标题 rowIndex++; foreach (string colTitle in columns.Values) { colIndex++; XF cellXF = doc.NewXF(); cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.HorizontalAlignment = HorizontalAlignments.Centered; cellXF.Font.Bold = true; cellXF.TopLineColor = Colors.Black; cellXF.TopLineStyle = 1; cellXF.BottomLineColor = Colors.Black; cellXF.BottomLineStyle = 1; cellXF.LeftLineColor = Colors.Black; cellXF.LeftLineStyle = 1; cellXF.RightLineColor = Colors.Black; cellXF.RightLineStyle = 1; sheet.Cells.Add(rowIndex, colIndex, colTitle, cellXF); } //数据行 for (int j = currentMinRowIndex; j < currentMaxRowIndex; j++) { rowIndex++; colIndex = 0; XF cellXF = doc.NewXF(); cellXF.TopLineColor = Colors.Black; cellXF.TopLineStyle = 1; cellXF.BottomLineColor = Colors.Black; cellXF.BottomLineStyle = 1; cellXF.LeftLineColor = Colors.Black; cellXF.LeftLineStyle = 1; cellXF.RightLineColor = Colors.Black; cellXF.RightLineStyle = 1; foreach (string colName in columns.Keys) { colIndex++; sheet.Cells.Add(rowIndex, colIndex, dt.Rows[j][colName] == null || dt.Rows[j][colName] == DBNull.Value ? "" : dt.Rows[j][colName].ToString(), cellXF); } } } doc.FileName = System.Web.HttpContext.Current.Server.UrlEncode(reportTitle) + ".xls"; if (!string.IsNullOrEmpty(filePath)) { doc.Save(filePath); } else { //把文件直接写到客户端 doc.Send(); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.End(); } }
测试用例：
private void Execel(DataTable dt) { //添加列 dt.Columns.Add(new DataColumn("C_TEST_E_NEW")); if (dt.Rows.Count > 0 && dt != null) { //数据转换 foreach (DataRow dr in dt.Rows) { dr["C_TEST_E_NEW"] = GetMap( dr["C_TEST_E"]); } var cols = new Dictionary<string, string>(); cols.Add("C_TEST_A", "测试A"); cols.Add("C_TEST_B, "测试B"); cols.Add("C_TEST_C", "测试C"); cols.Add("C_TEST_D", "测试D"); cols.Add("C_TEST_E", "测试E"); var subTitle = new List<string>(); subTitle.Add("导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); ExcelHelper.Export("ExportTitle", subTitle, dt, cols); }二、使用模板导出
public HSSFCell GetCell(HSSFRow row, HSSFCellStyle cellStyle, int colIdx) { HSSFCell cell = row.GetCell(colIdx) as HSSFCell; if (cell == null) { cell = row.CreateCell(colIdx) as HSSFCell; cell.CellStyle = cellStyle; } return cell; } public void ExportExcel(bool flag) { DataTable dt = GetTable(true); using (FileStream file = new FileStream(Server.MapPath("~/Test/ExportTest.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1") as HSSFSheet; HSSFCellStyle sheetStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle; sheetStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN; sheetStyle.BottomBorderColor = 64; sheetStyle.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN; sheetStyle.LeftBorderColor = 64; sheetStyle.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN; sheetStyle.RightBorderColor = 64; sheetStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN; sheetStyle.TopBorderColor = 64; sheetStyle.WrapText = true; sheet1.ForceFormulaRecalculation = true; for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; HSSFRow hssfRow = sheet1.CreateRow(sheet1.LastRowNum + 1) as HSSFRow; GetCell(hssfRow, sheetStyle, 0).SetCellValue(GetStreetName(row["C_TEST_A"])); GetCell(hssfRow, sheetStyle, 1).SetCellValue(GetValue(row["C_TEST_B"])); GetCell(hssfRow, sheetStyle, 2).SetCellValue(GetReason(row["C_TEST_C"])); GetCell(hssfRow, sheetStyle, 3).SetCellValue(GetValue(row["C_TEST_D"])); GetCell(hssfRow, sheetStyle, 4).SetCellValue(GetValue(row["C_TEST_E"])); } using (MemoryStream ms = new MemoryStream()) { hssfworkbook.Write(ms); Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.AddHeader("Content-Disposition", string.Format("attachment;filename=SiteInfoSearch.xls")); Response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312"); //中文标题 Response.BinaryWrite(ms.ToArray()); Response.End(); } } }
导出列表信息至Excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。