/// <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());
}
}
