/// <summary>
/// 检查模板与导出路径合法性
/// </summary>
/// <param name="templatePath"></param>
/// <param name="filePath"></param>
public static void CheckTemplateAndExportPath(string templatePath, ref string filePath)
{
if (!File.Exists(templatePath))
{
throw new FileNotFoundException(templatePath + "文件不存在！");
}
bool isCompatible = ExcelCommon.GetIsCompatible(templatePath);
if (string.IsNullOrEmpty(filePath))
{
filePath = ExcelCommon.GetSaveFilePath(isCompatible);
}
else if (isCompatible && !Path.GetExtension(filePath).Equals(".xls", StringComparison.OrdinalIgnoreCase))
{
throw new ArgumentException("当模板采用兼容模式时（低版本格式，如：xls,xlt），则指定的导出文件格式必需为xls。");
}
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径,可传空值</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = ExcelCommon.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return(null);
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = ExcelCommon.GetIsCompatible(excelFilePath);
return(ToDataTable(stream, sheetName, headerRowIndex, isCompatible));
}
}
/// <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>
/// 由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>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceDs">要导出数据的DataSet</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public static string ToExcel(DataSet sourceDs, string filePath = 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);
for (int i = 0; i < sourceDs.Tables.Count; i++)
{
DataTable table = sourceDs.Tables[i];
string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle>();
// handling header.
foreach (DataColumn column in table.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
colStyles[headerCell.ColumnIndex] = ExcelCommon.GetCellStyle(workbook);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.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();
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);
}
