/// <summary> /// 写入到文件(重名文件将会被覆盖) /// </summary> /// <param name="ds">数据源</param> /// <param name="filePathAndName">文件路径(*.xls)</param> /// <param name="useTitle">是否使用标题,默认使用</param> /// <param name="splitCount">每个Sheet最大行数(Excel 2003 最大限制 65536),最大不能超过 65536,注意该值不是一个精确值,可能在运行时被修改</param> /// <param name="option">Excel选项</param> public static void WriteToFile(DataSet ds, string filePathAndName, bool useTitle = true, int splitCount = 65536, ExcelOptions option = null) { if (string.IsNullOrWhiteSpace(filePathAndName)) { throw new ArgumentNullException("filePathAndName"); } //if (!Path.GetExtension(filePathAndName).EndsWith("xls", StringComparison.OrdinalIgnoreCase)) //{ // throw new ArgumentException("必需是后缀名为xls的文件"); //} var dir = Path.GetDirectoryName(filePathAndName); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } using (FileStream fs = new FileStream(filePathAndName, FileMode.Create)) { var bytes = WriteToBytes(ds, useTitle, splitCount, option); fs.Write(bytes, 0, bytes.Length); } }
/// <summary> /// 写入到Excel的Zip压缩文件 /// </summary> /// <param name="dt">表</param> /// <param name="useTitle">是否使用标题,默认使用</param> /// <param name="filePathAndName">文件路径</param> /// <param name="splitCount">每个Sheet最大行数(Excel 2003 最大限制 65536),最大不能超过 65536,注意该值不是一个精确值,可能在运行时被修改</param> /// <param name="option">Excel选项</param> /// <returns></returns> public static void WriteToZipFile(DataTable dt, string filePathAndName, bool useTitle = true, int splitCount = 65536, ExcelOptions option = null) { var ds = new DataSet(); ds.Tables.Add(dt); WriteToZipFile(ds, filePathAndName, useTitle, splitCount, option); }
/// <summary> /// 写入Excel的Zip压缩字节流 /// </summary> /// <param name="dt">表</param> /// <param name="useTitle">是否使用标题,默认使用</param> /// <param name="splitCount">每个Sheet最大行数(Excel 2003 最大限制 65536),最大不能超过 65536,注意该值不是一个精确值,可能在运行时被修改</param> /// <param name="option">Excel的选项</param> /// <returns></returns> public static byte[] WriteToZipBytes(DataTable dt, bool useTitle = true, int splitCount = 65536, ExcelOptions option = null) { if (dt == null) { return(null); } var ds = new DataSet(); ds.Tables.Add(dt); return(WriteToZipBytes(ds, useTitle, splitCount, option)); }
/// <summary> /// 写入Excel字节流 /// </summary> /// <param name="dt">表</param> /// <param name="useTitle">是否使用标题,默认使用</param> /// <param name="splitCount">每个Sheet最大行数(Excel 2003 最大限制 65536),最大不能超过 65536,注意该值不是一个精确值,可能在运行时被修改</param> /// <param name="option">Excel的选项,包括合并,样式等</param> /// <returns></returns> public static byte[] WriteToBytes(DataTable dt, bool useTitle = true, int splitCount = 65536, ExcelOptions option = null) { if (dt == null) { return(null); } //Excel最多 255个sheet if (splitCount < 1 || splitCount > 65536) { splitCount = 65536; } int maxSheet = 255; var sheetCount = dt.Rows.Count / (splitCount - (useTitle ? 1 : 0)) + (dt.Rows.Count % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1); while (sheetCount > maxSheet) { splitCount++; sheetCount = dt.Rows.Count / (splitCount - (useTitle ? 1 : 0)) + (dt.Rows.Count % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1); } int totalSheet = (dt.Rows.Count / splitCount) + (dt.Rows.Count % splitCount == 0 ? 0 : 1); int currentSheet = 0; int titleCount = useTitle ? 1 : 0; var sheetName = (string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet" : dt.TableName) + "_{0}"; var sheetKey = string.Format(sheetName, currentSheet); int rowIndex = 0; HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = null; IRow row = null; ICell cell = null; //直接输出标题 if (dt.Rows.Count == 0) { sheet = wk.CreateSheet(sheetKey); row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i, CellType.String); cell.SetCellValue(dt.Columns[i].ColumnName); } using (MemoryStream ms = new MemoryStream()) { wk.Write(ms); return(ms.ToArray()); } } for (int i = 0; i < dt.Rows.Count; i++) { if (sheet == null || rowIndex >= splitCount - titleCount) { currentSheet++; rowIndex = 0; sheetKey = string.Format(sheetName, currentSheet); sheet = wk.GetSheet(sheetKey) ?? wk.CreateSheet(sheetKey); row = sheet.CreateRow(0); int j = 0; foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(j, CellType.String); cell.SetCellValue(dc.ColumnName); j++; } } int k = 0; row = sheet.CreateRow(rowIndex + titleCount); foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(k, CellType.String); cell.SetCellValue(dt.Rows[i][k].ToString()); k++; } rowIndex++; } ApplyOptions(wk, useTitle, splitCount, option); using (MemoryStream ms = new MemoryStream()) { wk.Write(ms); return(ms.ToArray()); } }
private static void ApplyOptions(HSSFWorkbook wk, bool useTitle, int splitCount, ExcelOptions option) { try { #region 单元格样式 if (option != null && option.CellStyle != null && wk != null) { var cellStyle = wk.CreateCellStyle(); var cellStylex = wk.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; if (option.CellStyle.FontHeight.HasValue) { var font = wk.CreateFont(); font.FontHeight = option.CellStyle.FontHeight.Value * 100; font.FontName = "宋体"; cellStyle.SetFont(font); } if (option.CellStyle.UseBorder) { cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; } cellStylex.CloneStyleFrom(cellStyle); if (option.CellStyle.UseSkipColor) { HSSFPalette palette = wk.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228); var color = palette.FindColor((byte)184, (byte)204, (byte)228); cellStylex.FillBackgroundColor = color.Indexed; cellStylex.FillPattern = FillPattern.SolidForeground; } for (int i = 0; i < wk.NumberOfSheets; i++) { var sheet = wk.GetSheetAt(i); if (sheet != null) { for (int j = sheet.FirstRowNum; j <= sheet.LastRowNum; j++) { var row = sheet.GetRow(j); if (row != null) { for (int k = row.FirstCellNum; k <= row.LastCellNum; k++) { var cell = row.GetCell(k); if (cell != null) { if (j % 2 == 0) { cell.CellStyle = cellStyle; } else { cell.CellStyle = cellStylex; } } } if (option.AutoSizeColumn) { sheet.AutoSizeColumn(j); } } } } } } #endregion #region 合并区域参数 if (option != null && option.MergedRegions != null && wk != null) { //不允许区域相交 if (option.IsValidMergedRegions) { foreach (var mg in option.MergedRegions) { //先确定属于那个Excel var startSheetIndex = (mg.FirstRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.FirstRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1; var endSheetIndex = (mg.LastRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.LastRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1; var firstRow = 0; var lastRow = 0; //合并区域在同一Sheet if (startSheetIndex == endSheetIndex) { firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); lastRow = mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); } else //if (endSheetIndex - startSheetIndex > 1) { firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); lastRow = wk.GetSheetAt(startSheetIndex).LastRowNum; wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); for (int i = startSheetIndex + 1; i <= endSheetIndex; i++) { firstRow = wk.GetSheetAt(i).FirstRowNum + (useTitle ? 1 : 0); lastRow = i == endSheetIndex ? (mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0)) : wk.GetSheetAt(i).LastRowNum; wk.GetSheetAt(i).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); } } } } } #endregion } catch { } }
/// <summary> /// 写入Excel字节流,多个表分成多个Sheet输出,每个表数据不能超过65536 /// </summary> /// <param name="ds">DataSet</param> /// <param name="useTitle">是否使用标题,默认使用</param> /// <param name="splitCount">每个Sheet最大行数(Excel 2003 最大限制 65536),最大不能超过 65536,注意该值不是一个精确值,可能在运行时被修改</param> /// <param name="option">Excel的选项,包括合并,样式等</param> /// <returns></returns> public static byte[] WriteToBytes(DataSet ds, bool useTitle = true, int splitCount = 65536, ExcelOptions option = null) { if (ds == null) { return(null); } int titleCount = useTitle ? 1 : 0; HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = null; IRow row = null; ICell cell = null; foreach (DataTable dt in ds.Tables) { int currentSheet = 0; var sheetName = string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet_{0}" : dt.TableName; var sheetKey = string.Format(sheetName, currentSheet); int rowIndex = 0; sheet = null; //直接输出标题 if (dt.Rows.Count == 0) { sheet = wk.CreateSheet(sheetKey); row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i, CellType.String); cell.SetCellValue(dt.Columns[i].ColumnName); } //using (MemoryStream ms = new MemoryStream()) //{ // wk.Write(ms); // return ms.ToArray(); //} } for (int i = 0; i < dt.Rows.Count; i++) { if (sheet == null || rowIndex >= splitCount - titleCount) { currentSheet++; rowIndex = 0; sheetKey = string.Format(sheetName, currentSheet); sheet = wk.GetSheet(sheetKey) ?? wk.CreateSheet(sheetKey); row = sheet.CreateRow(0); int j = 0; foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(j, CellType.String); cell.SetCellValue(dc.ColumnName); j++; } } int k = 0; row = sheet.CreateRow(rowIndex + titleCount); foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(k, CellType.String); cell.SetCellValue(dt.Rows[i][k].ToString()); k++; } rowIndex++; } } ApplyOptions(wk, useTitle, splitCount, option); using (MemoryStream ms = new MemoryStream()) { wk.Write(ms); return(ms.ToArray()); } }