/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <param name="colNames">需要导出的列名,可选</param>
/// <param name="colAliasNames">导出的列名重命名，可选</param>
/// <param name="colDataFormats">列格式化集合，可选</param>
/// <param name="sheetSize">指定每个工作薄显示的记录数，可选（不指定或指定小于0，则表示只生成一个工作薄）</param>
/// <returns></returns>
public static string ToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null, string[] colNames = null, IDictionary <string, string> colAliasNames = null, IDictionary <string, string> colDataFormats = null, int sheetSize = 0)
{
if (sourceTable.Rows.Count <= 0)
{
return(null);
}
if (string.IsNullOrEmpty(filePath))
{
filePath = ExcelCommon.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath))
{
return(null);
}
bool isCompatible = ExcelCommon.GetIsCompatible(filePath);
IWorkbook workbook = ExcelCommon.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = ExcelCommon.GetCellStyle(workbook, true);
//ICellStyle cellStyle = Common.GetCellStyle(workbook);
if (colNames == null || colNames.Length <= 0)
{
colNames = sourceTable.Columns.Cast <DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();
}
IEnumerable <DataRow> batchDataRows, dataRows = sourceTable.Rows.Cast <DataRow>();
int sheetCount = 0;
if (sheetSize <= 0)
{
sheetSize = sourceTable.Rows.Count;
}
while ((batchDataRows = dataRows.Take(sheetSize)).Count() > 0)
{
Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle>();
ISheet sheet = workbook.CreateSheet(sheetName + (++sheetCount).ToString());
IRow headerRow = sheet.CreateRow(0);
// handling header.
for (int i = 0; i < colNames.Length; i++)
{
ICell headerCell = headerRow.CreateCell(i);
if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i]))
{
headerCell.SetCellValue(colAliasNames[colNames[i]]);
}
else
{
headerCell.SetCellValue(colNames[i]);
}
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
if (colDataFormats != null && colDataFormats.ContainsKey(colNames[i]))
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyleWithDataFormat(workbook, colDataFormats[colNames[i]]);
}
else
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyle(workbook);
}
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in batchDataRows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < colNames.Length; i++)
{
ICell cell = dataRow.CreateCell(i);
//cell.SetCellValue((row[colNames[i]] ?? "").ToString());
//cell.CellStyle = cellStyle;
ExcelCommon.SetCellValue(cell, (row[colNames[i]] ?? "").ToString(), sourceTable.Columns[colNames[i]].DataType, colStyles);
ExcelCommon.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
dataRows = dataRows.Skip(sheetSize);
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
workbook = null;
return(filePath);
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="colAliasNames">导出的列名重命名数组</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <param name="colDataFormats">列格式化集合，可选</param>
/// <returns></returns>
public static string ToExcel(DataTable sourceTable, string[] colAliasNames, string sheetName = "result", string filePath = null, IDictionary <string, string> colDataFormats = null)
{
if (sourceTable.Rows.Count <= 0)
{
return(null);
}
if (string.IsNullOrEmpty(filePath))
{
filePath = ExcelCommon.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath))
{
return(null);
}
if (colAliasNames == null || sourceTable.Columns.Count != colAliasNames.Length)
{
throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames");
}
bool isCompatible = ExcelCommon.GetIsCompatible(filePath);
IWorkbook workbook = ExcelCommon.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = ExcelCommon.GetCellStyle(workbook, true);
//ICellStyle cellStyle = Common.GetCellStyle(workbook);
Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle>();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(colAliasNames[column.Ordinal]);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
if (colDataFormats != null && colDataFormats.ContainsKey(column.ColumnName))
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyleWithDataFormat(workbook, colDataFormats[column.ColumnName]);
}
else
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyle(workbook);
}
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
ICell cell = dataRow.CreateCell(column.Ordinal);
//cell.SetCellValue((row[column] ?? "").ToString());
//cell.CellStyle = cellStyle;
ExcelCommon.SetCellValue(cell, (row[column] ?? "").ToString(), column.DataType, colStyles);
ExcelCommon.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return(filePath);
}
/// <summary>
/// 由DataGridView导出
/// </summary>
/// <param name="grid">要导出的DataGridView对象</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <param name="includeHiddenCol">导出时是否包含隐藏列，可选</param>
/// <param name="colHeaderTexts">指定导出DataGridView的列标题名数组，可选</param>
/// <param name="colDataFormats">列格式化集合，可选</param>
/// <returns></returns>
public static string ToExcel(DataGridView grid, string sheetName = "result", string filePath = null, bool includeHiddenCol = false, string[] colHeaderTexts = null, IDictionary <string, string> colDataFormats = null)
{
if (grid.Rows.Count <= 0)
{
return(null);
}
if (string.IsNullOrEmpty(filePath))
{
filePath = ExcelCommon.GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath))
{
return(null);
}
bool isCompatible = ExcelCommon.GetIsCompatible(filePath);
DataGridViewColumn[] expCols = null;
expCols = grid.Columns.Cast <DataGridViewColumn>().OrderBy(c => c.DisplayIndex).ToArray();
if (!includeHiddenCol)
{
expCols = expCols.Where(c => c.Visible).ToArray();
}
if (colHeaderTexts != null && colHeaderTexts.Length > 0)
{
expCols = expCols.Where(c => colHeaderTexts.Contains(c.HeaderText)).ToArray();
}
IWorkbook workbook = ExcelCommon.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = ExcelCommon.GetCellStyle(workbook, true);
//ICellStyle cellStyle = Common.GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle>();
for (int i = 0; i < expCols.Length; i++)
{
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(expCols[i].HeaderText);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
if (colDataFormats != null && colDataFormats.ContainsKey(expCols[i].HeaderText))
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyleWithDataFormat(workbook, colDataFormats[expCols[i].HeaderText]);
}
else
{
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyle(workbook);
}
}
int rowIndex = 1;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < expCols.Length; n++)
{
ICell cell = dataRow.CreateCell(n);
//cell.SetCellValue((row.Cells[expCols[n].Index].Value ?? "").ToString());
//cell.CellStyle = cellStyle;
ExcelCommon.SetCellValue(cell, (row.Cells[expCols[n].Index].Value ?? "").ToString(), expCols[n].ValueType, colStyles);
ExcelCommon.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return(filePath);
}