/// <summary>
/// 数据导出excel
/// </summary>
/// <param name="tableName">表名,对应dataSet中的table名（用于在OutPutClass参数中查找字段的对应关系，当没有设置字段对应关系时可以为null）</param>
/// <param name="outPutClass">DataSet中的字段与Sheet中实际显示的字段对应关系设置（为null时，则使用DataSet中的列名）</param>
/// <param name="ds">要导出的DataSet</param>
/// <param name="fileTitle">导出的EXCEL文件的名字（仅适用于Http环境）</param>
/// <param name="conTitle">Excel中每个Sheet的名称</param>
public static void OutPutExcel(string[] tableName, List <OutPutClass> outPutClass, DataSet ds, string fileTitle, string[] conTitle)
{
OutPutParamClass paramClass = new OutPutParamClass();
paramClass.TableName = tableName;
paramClass.OutPutClass = outPutClass;
paramClass.Ds = ds;
paramClass.FileTitle = fileTitle;
paramClass.ConTitle = conTitle;
OutPutExcel(paramClass);
}
/// <summary>
/// 数据导出excel
/// </summary>
/// <param name="paramClass">导出参数</param>
public static void OutPutExcel(OutPutParamClass paramClass)
{
StringBuilder str = new StringBuilder();
#region 合法性检测
if (null == paramClass.Ds || paramClass.Ds.Tables.Count == 0)
{
str.Append("要导出的数据不能为空，导出失败！；");
}
if (null != paramClass.ConTitle && null != paramClass.Ds)
{
if (paramClass.ConTitle.Length != paramClass.Ds.Tables.Count)
{
str.Append("Sheet的名称信息与要导出的数据源的数量不匹配，每一个Sheet必须要有一个名称，导出失败！；");
}
if (paramClass.ConTitle.Distinct().Count() != paramClass.ConTitle.Length)
{
str.Append("Sheet的名称信息不能重复，导出失败！；");
}
}
if (null != paramClass.OutPutClass && paramClass.OutPutClass.Count > 0)
{
if (paramClass.TableName.Length != paramClass.Ds.Tables.Count)
{
str.Append("表名与dataSet的table数量不一致，导出失败！；");
}
}
if (str.Length > 0)
{
throw new ArgumentException(str.ToString(), "paramClass");
}
#endregion 合法性检测
#region 是否指定被操作的工作薄
Workbook workbook = null;
if (!string.IsNullOrEmpty(paramClass.WorkBookFilePath))
{
workbook = new Workbook(paramClass.WorkBookFilePath);
}
else
{
workbook = new Workbook();
}
#endregion 是否指定被操作的工作薄
for (int i = 0; i < paramClass.Ds.Tables.Count; i++)
{
Worksheet sheet = workbook.Worksheets[i];
if (null != paramClass.ConTitle && paramClass.ConTitle.Length > 0)
{
sheet.Name = paramClass.ConTitle[i];
}
if (i != paramClass.Ds.Tables.Count - 1)
{
workbook.Worksheets.Add();
}
DataTable dt = paramClass.Ds.Tables[i];
List <string> dtColNameLst = new List <string>();
for (int k = 0; k < dt.Columns.Count; k++)
{
dtColNameLst.Add(dt.Columns[k].ColumnName);
}
#region 写入列名
List <string> newNamesLst = new List <string>();
if (null != paramClass.OutPutClass && paramClass.OutPutClass.Count > 0)
{
OutPutClass outPutModel = paramClass.OutPutClass.First(k => k.TableName == paramClass.TableName[i].Trim());
foreach (var m in outPutModel.Fields)
{
newNamesLst.Add(m.newName);
if (dtColNameLst.Contains(m.oldName))
{
dt.Columns[m.oldName].ColumnName = m.newName;
}
}
}
else
{
newNamesLst = dtColNameLst;
}
#endregion 写入列名
#region 向sheet中写入数据
dt.AcceptChanges();
sheet.Cells.ImportDataTable(dt.DefaultView.ToTable("dtNew", true, newNamesLst.ToArray()), paramClass.IsShowFieldLine, paramClass.FirstRowIndex, paramClass.FirstColumnIndex, dt.Rows.Count + 1, dt.Columns.Count, true, "yyyy-MM-dd HH:mm:ss");
sheet.AutoFitColumns();
#endregion 向sheet中写入数据
#region 添加样式
Cells cells = sheet.Cells;
if (paramClass.IsShowCustomLine)
{
cells[0, 0].Value = string.Format("数据导出：{0}；导出时间：{1}；记录总数：{2}", paramClass.ConTitle[i], DateTime.Now, dt.Rows.Count);
Aspose.Cells.Style styleCell0 = cells[0, 0].GetStyle();
styleCell0.Font.Color = System.Drawing.Color.Red;
cells[0, 0].SetStyle(styleCell0);
}
if (paramClass.IsShowFieldLine)
{
Range range = sheet.Cells.CreateRange(1, 0, 1, newNamesLst.Count);
range.Name = "Range1";
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = System.Drawing.Color.Blue;
style.Font.IsBold = true;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
range.ApplyStyle(style, styleFlag);
}
#endregion 添加样式
}
paramClass.GetWorkBook = workbook;
#region 保存
if (!string.IsNullOrEmpty(paramClass.CustomFileName))
{
workbook.Save(paramClass.CustomFileName, paramClass.SaveFormat);
}
if (paramClass.AutoDownLoad)
{
string fileName = string.Format("{0}_数据导出.xlsx", paramClass.FileTitle);
bool isFirefox = HttpContext.Current.Request.Browser.Type.ToLower().Contains("firefox");
fileName = isFirefox ? fileName : HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
workbook.Save(HttpContext.Current.Response, fileName, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
#endregion 保存
}
