/// <summary>
/// 导出Excel
/// </summary>
/// <param name="filePath">导出的文件路径</param>
/// <param name="fileTemplatePath">模板路径</param>
/// <exception cref="Exception">
/// </exception>
public void ExcelExport(DataSet ds, string filePath, string fileTemplatePath)
{
try
{
System.IO.File.Copy(fileTemplatePath, filePath);
}
catch (Exception ex)
{
throw new Exception("复制Excel文件出错" + ex.Message);
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
var sheetExs = document.GetWorksheets();
for (int s = 0; s < sheetExs.Count; s++)
{
if (s >= ds.Tables.Count)
{
break;
}
DataTable dt = ds.Tables[s];
if (dt == null || dt.Rows.Count < 1)
{
continue;
}
List <string> listCol = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
listCol.Add(dc.ColumnName);
}
var sheetEx = sheetExs[s];
var sheetData = sheetEx.Worksheet.GetFirstSheetData();
OpenXml.CellStyleIndex = 1;
////写标题相关信息
//this.UpdateTitleText(sheetData);
int startRowIndex = 0;
if (RowIndex != null && RowIndex.Length > 0)
{
startRowIndex = RowIndex[s];
}
for (var i = 0; i < dt.Rows.Count; i++)
{
var rowIndex = startRowIndex + i;
DataRow dr = dt.Rows[i];
for (int l = 0; l < listCol.Count; l++)
{
string cellName = IntToMoreChar(l + 1) + rowIndex;
sheetData.SetCellValue(cellName, dr[listCol[l]]);
}
}
if (OpenXmlExportImages != null && OpenXmlExportImages.Count > 0 && OpenXmlExportImages.ContainsKey(sheetEx.SheetName))
{
var exportImages = OpenXmlExportImages[sheetEx.SheetName];
if (exportImages != null)
{
foreach (var img in exportImages)
{
document.InsertImage(sheetEx.SheetId, img.X, img.Y, img.Width, img.Height, img.ImagePath);
}
}
}
}
// var str = OpenXmlHelper.ValidateDocument(document);验证生成的Excel
}
}