/// <summary>
/// 数据导出到Excel, 自定义excel样式
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="zeroColumns">需要清空零值的字段集合</param>
/// <param name="redStyleColumns">需要设置红色背景的字段集合</param>
/// <param name="centerStyleColumns">需要设置标题行文本居中的字段集合</param>
/// <param name="contentLeftStyleColumns">需要设置内容靠左显示的字段集合</param>
/// <param name="dictWidthColumns">导出excel列宽字典</param>
/// <param name="filePath">保存文件夹路径</param>
/// <param name="fileName">保存文件名称</param>
/// <param name="saveFileType">保存文件类型（.xls;.xlsx）</param>
/// <returns>文件物理路径</returns>
public static string SaveDataTableToExcel2(DataTable dt, List <string> zeroColumns, List <string> redStyleColumns, List <string> centerStyleColumns, List <string> contentLeftStyleColumns, Dictionary <string, double> dictWidthColumns, string filePath, string fileName, EnumExcelFileType saveFileType)
{
string savePath = Path.Combine(filePath, fileName);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
try
{
Workbook workBook = new Workbook();
workBook.Worksheets.Clear();
workBook.Worksheets.Add(dt.TableName);
Worksheet ws = workBook.Worksheets[dt.TableName];
ws.FreezePanes(1, 1, 1, 0); //冻结第一行
//设置标题样式
Aspose.Cells.Style headerStyle = workBook.CreateStyle();
headerStyle.HorizontalAlignment = TextAlignmentType.Right; //文本水平显示方式
headerStyle.Font.Color = System.Drawing.Color.White; //字体颜
headerStyle.Font.Name = "宋体"; //文字字体
headerStyle.Font.Size = 10; //文字大小
headerStyle.Font.IsBold = true; //粗体
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(91, 155, 213); //背景色
headerStyle.Pattern = Aspose.Cells.BackgroundType.Solid; //单元格的线：实线
//设置内容列样式
Aspose.Cells.Style contentStyle = workBook.CreateStyle();
contentStyle.HorizontalAlignment = TextAlignmentType.Center; //文本水平显示方式
//设置验证失败的内容样式1
Aspose.Cells.Style errorStyle1 = workBook.CreateStyle();
errorStyle1.Font.Color = System.Drawing.Color.Red;
//设置验证失败的内容样式2
Aspose.Cells.Style errorStyle2 = workBook.CreateStyle();
headerStyle.ForegroundColor = System.Drawing.Color.Red;
headerStyle.Pattern = Aspose.Cells.BackgroundType.Solid;
int rowscount = dt.Rows.Count;
for (int col = 0; col < dt.Columns.Count; col++)
{
ws.Cells[0, col].PutValue(dt.Columns[col].Caption);
//设置标题行文本水平显示方式
if (centerStyleColumns.Contains(dt.Columns[col].Caption))
{
headerStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
headerStyle.HorizontalAlignment = TextAlignmentType.Right;
}
//设置标题行背景色
if (redStyleColumns.Contains(dt.Columns[col].Caption))
{
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(192, 0, 0);
}
else
{
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(91, 155, 213);
}
//设置标题行样式
ws.Cells[0, col].SetStyle(headerStyle);
//设置列宽
ws.Cells.SetColumnWidth(col, dictWidthColumns[dt.Columns[col].Caption]);
}
for (int col = 0; col < dt.Columns.Count; col++)
{
//设置内容行文本水平显示方式
if (contentLeftStyleColumns.Contains(dt.Columns[col].Caption))
{
contentStyle.HorizontalAlignment = TextAlignmentType.Left;
}
else
{
contentStyle.HorizontalAlignment = TextAlignmentType.Center;
}
if (zeroColumns.Contains(dt.Columns[col].Caption))
{
for (int row = 0; row < rowscount; row++)
{
if (dt.Rows[row].ItemArray[col].ToString() == "0" || dt.Rows[row].ItemArray[col].ToString() == "0.0" || dt.Rows[row].ItemArray[col].ToString() == "0.00")
{
ws.Cells[row + 1, col].PutValue("");
}
else
{
ws.Cells[row + 1, col].PutValue(dt.Rows[row].ItemArray[col].ToString());
}
//设置内容行样式
ws.Cells[row + 1, col].SetStyle(contentStyle);
}
}
else
{
for (int row = 0; row < rowscount; row++)
{
ws.Cells[row + 1, col].PutValue(dt.Rows[row].ItemArray[col].ToString());
//设置内容行样式
ws.Cells[row + 1, col].SetStyle(contentStyle);
}
}
}
//if (dt.Columns.Count > 0)
//{
// ws.AutoFitColumns(); //自动适应宽度
//}
if (dt.Rows.Count > 0)
{
ws.AutoFitRows(); //自动适应高度
}
if (saveFileType == EnumExcelFileType.Excel2003)
{
workBook.Save(savePath, SaveFormat.Excel97To2003);
}
else
{
workBook.Save(savePath, SaveFormat.Xlsx);
}
}
catch (Exception e)
{
LogScopeHelper.Error(e.Message, e);
return(string.Empty);
}
return(savePath);
}
/// <summary>
/// 数据导出到Excel（支持多表导出多个Sheet页）
/// </summary>
/// <param name="dataset">数据集</param>
/// <param name="filePath">保存文件夹路径</param>
/// <param name="fileName">保存文件名称</param>
/// <param name="saveFileType">保存文件类型（.xls;.xlsx）</param>
/// <returns>文件物理路径</returns>
public static string SaveDataSetToExcel(DataSet dataset, string filePath, string fileName, EnumExcelFileType saveFileType)
{
string savePath = Path.Combine(filePath, fileName);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
try
{
Workbook workBook = new Workbook();
workBook.Worksheets.Clear();
foreach (DataTable dt in dataset.Tables)
{
workBook.Worksheets.Add(dt.TableName);
Worksheet ws = workBook.Worksheets[dt.TableName];
ws.FreezePanes(1, 1, 1, 0); //冻结第一行
int rowscount = dt.Rows.Count;
for (int col = 0; col < dt.Columns.Count; col++)
{
ws.Cells[0, col].PutValue(dt.Columns[col].Caption);
}
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < rowscount; row++)
{
ws.Cells[row + 1, col].PutValue(dt.Rows[row].ItemArray[col].ToString());
}
}
if (dt.Columns.Count > 0)
{
ws.AutoFitColumns();
}
if (dt.Rows.Count > 0)
{
ws.AutoFitRows();
}
}
if (saveFileType == EnumExcelFileType.Excel2003)
{
workBook.Save(savePath, SaveFormat.Excel97To2003);
}
else
{
workBook.Save(savePath, SaveFormat.Xlsx);
}
}
catch (Exception e)
{
LogScopeHelper.Error(e.Message, e);
return(string.Empty);
}
return(savePath);
}
/// <summary>
/// 数据导出到Excel, 自定义excel样式
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="numberColumns">需要设置单元格为数值列的字段集合</param>
/// <param name="redStyleColumns">需要设置红色背景的字段集合</param>
/// <param name="centerStyleColumns">需要设置标题行文本居中的字段集合</param>
/// <param name="contentLeftStyleColumns">需要设置内容靠左显示的字段集合</param>
/// <param name="dictWidthColumns">导出excel列宽字典</param>
/// <param name="filePath">保存文件夹路径</param>
/// <param name="fileName">保存文件名称</param>
/// <param name="saveFileType">保存文件类型（.xls;.xlsx）</param>
/// <returns>文件物理路径</returns>
public static string SaveDataTableToExcel(DataTable dt, List <string> numberColumns, List <string> redStyleColumns, List <string> centerStyleColumns, List <string> contentLeftStyleColumns, Dictionary <string, double> dictWidthColumns, string filePath, string fileName, EnumExcelFileType saveFileType)
{
string savePath = Path.Combine(filePath, fileName);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
try
{
Workbook workBook = new Workbook();
workBook.Worksheets.Clear();
workBook.Worksheets.Add(dt.TableName);
Worksheet ws = workBook.Worksheets[dt.TableName];
ws.FreezePanes(1, 1, 1, 0); //冻结第一行
//设置标题样式
Aspose.Cells.Style headerStyle = workBook.CreateStyle();
headerStyle.HorizontalAlignment = TextAlignmentType.Right; //文本水平显示方式
headerStyle.Font.Color = System.Drawing.Color.White; //字体颜
headerStyle.Font.Name = "宋体"; //文字字体
headerStyle.Font.Size = 10; //文字大小
headerStyle.Font.IsBold = true; //粗体
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(91, 155, 213); //背景色
headerStyle.Pattern = Aspose.Cells.BackgroundType.Solid; //单元格的线：实线
//设置内容列样式
Aspose.Cells.Style contentStyle = workBook.CreateStyle();
contentStyle.HorizontalAlignment = TextAlignmentType.Center; //文本水平显示方式
//设置验证失败的内容样式1
Aspose.Cells.Style errorStyle1 = workBook.CreateStyle();
errorStyle1.HorizontalAlignment = TextAlignmentType.Center;
errorStyle1.Font.Color = System.Drawing.Color.Red;
//设置验证失败的内容样式2
Aspose.Cells.Style errorStyle2 = workBook.CreateStyle();
errorStyle2.HorizontalAlignment = TextAlignmentType.Center;
errorStyle2.ForegroundColor = System.Drawing.Color.Red;
errorStyle2.Pattern = Aspose.Cells.BackgroundType.Solid;
int rowscount = dt.Rows.Count;
for (int col = 0; col < dt.Columns.Count; col++)
{
ws.Cells[0, col].PutValue(dt.Columns[col].Caption);
//设置标题行文本水平显示方式
if (centerStyleColumns.Contains(dt.Columns[col].Caption))
{
headerStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
headerStyle.HorizontalAlignment = TextAlignmentType.Right;
}
//设置标题行背景色
if (redStyleColumns.Contains(dt.Columns[col].Caption))
{
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(192, 0, 0);
}
else
{
headerStyle.ForegroundColor = System.Drawing.Color.FromArgb(91, 155, 213);
}
//设置标题行样式
ws.Cells[0, col].SetStyle(headerStyle);
//设置列宽
ws.Cells.SetColumnWidth(col, dictWidthColumns[dt.Columns[col].Caption]);
}
for (int col = 0; col < dt.Columns.Count; col++)
{
//设置内容行文本水平显示方式
if (contentLeftStyleColumns.Contains(dt.Columns[col].Caption))
{
contentStyle.HorizontalAlignment = TextAlignmentType.Left;
}
else
{
contentStyle.HorizontalAlignment = TextAlignmentType.Center;
}
bool styleFlag = true;
for (int row = 0; row < rowscount; row++)
{
styleFlag = true;
ws.Cells[row + 1, col].PutValue(dt.Rows[row].ItemArray[col].ToString());
//设置内容行样式
ws.Cells[row + 1, col].SetStyle(contentStyle);
//物品名称为空
if (dt.Columns[col].Caption == "货物明细信息|物品名称①" && string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//结算重量是否超过10kg
if (dt.Columns[col].Caption == "结算重量" && !string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) && Convert.ToDecimal(dt.Rows[row].ItemArray[col]) > 10m)
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//保价是否超过10000
if (dt.Columns[col].Caption == "保价" && !string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) && Convert.ToDecimal(dt.Rows[row].ItemArray[col]) > 10000m)
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//物品名称不为空, 税关号为空
if (dt.Columns[col].Caption == "货物明细信息|税关号①" && string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) && !string.IsNullOrEmpty(dt.Rows[row].ItemArray[col - 1].ToString()))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
if (dt.Columns[col].Caption == "货物明细信息|税关号②" && string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) && !string.IsNullOrEmpty(dt.Rows[row].ItemArray[col - 1].ToString()))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
if (dt.Columns[col].Caption == "货物明细信息|税关号③" && string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) && !string.IsNullOrEmpty(dt.Rows[row].ItemArray[col - 1].ToString()))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//收件地邮编为空或不是有效的邮编
if (dt.Columns[col].Caption == "收件人信息|收件地邮编" && (string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) || !Regex.IsMatch(dt.Rows[row].ItemArray[col].ToString(), @"^\d{6}$")))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//收件人电话为空或不是有效的11位手机号码
if (dt.Columns[col].Caption == "收件人信息|收件人电话" && (string.IsNullOrEmpty(dt.Rows[row].ItemArray[col].ToString()) || !Regex.IsMatch(dt.Rows[row].ItemArray[col].ToString(), @"^1[1-9]\d{9}$")))
{
styleFlag = false;
ws.Cells[row + 1, col].SetStyle(errorStyle2);
}
//税金超出允许范围
//设置运单编号单元格样式
if (!styleFlag)
{
ws.Cells[row + 1, 1].SetStyle(errorStyle1);
}
//设置单元格数值列属性
if (numberColumns.Contains(dt.Columns[col].Caption))
{
//继承原有样式
Aspose.Cells.Style numberStyle = ws.Cells[row + 1, col].GetStyle();
numberStyle.Number = 4;
ws.Cells[row + 1, col].SetStyle(numberStyle);
}
}
}
//if (dt.Columns.Count > 0)
//{
// ws.AutoFitColumns(); //自动适应宽度
//}
if (dt.Rows.Count > 0)
{
ws.AutoFitRows(); //自动适应高度
}
if (saveFileType == EnumExcelFileType.Excel2003)
{
workBook.Save(savePath, SaveFormat.Excel97To2003);
}
else
{
workBook.Save(savePath, SaveFormat.Xlsx);
}
}
catch (Exception e)
{
LogScopeHelper.Error(e.Message, e);
return(string.Empty);
}
return(savePath);
}
