/// <summary>
/// DataTable数据表保存至Excel （合并行报表）【年度日报表】
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <param name="excelParm"></param>
public static void ToExcel1(DataTable dt, string filePath, ExcelParm excelParm)
{
string unitId = Tools.GetAppSetting("UnitId");
string subTitle = string.Empty;
if (!string.IsNullOrEmpty(excelParm.GrossWorker))
{
subTitle += "二次司磅员：" + excelParm.GrossWorker + " ";
}
if (!string.IsNullOrEmpty(excelParm.CustomerName))
{
subTitle += "客户名称：" + excelParm.CustomerName + " ";
}
if (!string.IsNullOrEmpty(excelParm.MaterialName))
{
subTitle += "物料名称：" + excelParm.MaterialName + " ";
}
if (!string.IsNullOrEmpty(excelParm.CarNumber))
{
subTitle += "车号：" + excelParm.CarNumber + " ";
}
if (!string.IsNullOrEmpty(excelParm.ContractCode))
{
subTitle += "合同号：" + excelParm.ContractCode + " ";
}
if (!string.IsNullOrEmpty(excelParm.Datatime1) && !string.IsNullOrEmpty(excelParm.Datatime2))
{
subTitle += "日期：" + excelParm.Datatime1 + " 至 " + excelParm.Datatime2;
}
//新建工作簿
Workbook wb = new Workbook();
//新建工作表
Worksheet ws = wb.Worksheets[0];
ws.Name = dt.TableName;
int rowIndex = 3;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
ws.Cells.SetRowHeight(rowIndex, 25);//设置行高
//创建样式
Style style = wb.Styles[wb.Styles.Add()];//新增样式
style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
style.Font.Name = "宋体"; //字体
style.Font.IsBold = true; //设置粗体
//style.Font.Color = Color.White;//设置字体颜色
style.Font.Size = 10; //设置字体大小
//style.ForegroundColor = Color.FromArgb(0, 196, 180); //背景色
style.Pattern = BackgroundType.Solid;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
//列名的处理
for (int i = 0; i < colCount; i++)
{
ws.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
ws.Cells[rowIndex, colIndex].SetStyle(style);//给单元格关联样式
colIndex++;
}
Style style2 = wb.Styles[wb.Styles.Add()];//新增样式
style2.Font.Name = "宋体";//文字字体
style2.Font.Size = 10;//文字大小
style2.ShrinkToFit = true;
style2.VerticalAlignment = TextAlignmentType.Center;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].Color = Color.Black;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].Color = Color.Black;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.LeftBorder].Color = Color.Black;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].Color = Color.Black;
Style style3 = wb.Styles[wb.Styles.Add()];//组织标题样式
style3.Font.Name = "宋体";//文字字体
style3.Font.Size = 16;//文字大小
style3.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells.SetRowHeight(0, 25);//设置行高
ws.Cells.Merge(0, 0, 1, dt.Columns.Count);
ws.Cells[0, 0].PutValue(excelParm.OrganName);
ws.Cells[0, 0].SetStyle(style3);
Style style5 = wb.Styles[wb.Styles.Add()];//标题样式
style5.Font.Name = "宋体";//文字字体
style5.Font.IsBold = true; //设置粗体
style5.Font.Size = 14;//文字大小
style5.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells.SetRowHeight(1, 25);//设置行高
ws.Cells.Merge(1, 0, 1, dt.Columns.Count);
ws.Cells[1, 0].PutValue(dt.TableName);
ws.Cells[1, 0].SetStyle(style5);
Style style4 = wb.Styles[wb.Styles.Add()];//新增查询条件标题样式
style4.Font.Name = "宋体";//文字字体
style4.Font.IsBold = true; //设置粗体
style4.Font.Size = 10;//文字大小
ws.Cells.SetRowHeight(2, 25);//设置行高
ws.Cells.Merge(2, 0, 1, dt.Columns.Count);
ws.Cells[2, 0].PutValue(subTitle);
ws.Cells[2, 0].SetStyle(style4);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
ws.Cells.SetRowHeight(rowIndex, 25);//设置行高
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
ws.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString() == "" ? null : dt.Rows[i][j].ToString());
style2.ForegroundColor = Color.White;
style2.Pattern = BackgroundType.Solid;
ws.Cells[rowIndex, colIndex].SetStyle(style2);//给单元格关联样式
colIndex++;
}
rowIndex++;
}
//设置所有列为自适应列宽
ws.AutoFitColumns();
for (int col = 0; col < colCount; col++)
{
ws.Cells.SetColumnWidthPixel(col, ws.Cells.GetColumnWidthPixel(col) + 20);
}
#region 合并单元格
int mergeDateStart = 4;
int mergeDate = 1;
int mergeAreaStart = 4;
int mergeArea = 1;
for (var i = 0; i < dt.Rows.Count; i++)
{
if (i + 1 < dt.Rows.Count)
{
//日期
if (dt.Rows[i]["日期"].ToString() != dt.Rows[i + 1]["日期"].ToString())
{
ws.Cells.Merge(mergeDateStart, 0, mergeDate, 1);
mergeDateStart += mergeDate;
mergeDate = 1;
}
else
{
mergeDate++;
}
//区域
if (dt.Rows[i]["区域"].ToString() != dt.Rows[i + 1]["区域"].ToString())
{
ws.Cells.Merge(mergeAreaStart, 1, mergeArea, 1);
mergeAreaStart += mergeArea;
mergeArea = 1;
}
else
{
mergeArea++;
}
}
else
{
//日期
ws.Cells.Merge(mergeDateStart, 0, mergeDate, 1);
//区域
ws.Cells.Merge(mergeAreaStart, 1, mergeArea, 1);
}
}
#endregion
string fullUpLoadPath = HttpContext.Current.Server.MapPath("~/UpLoad/Excel/");
//检查本地上传的物理路径是否存在，不存在则创建
if (!System.IO.Directory.Exists(fullUpLoadPath))
{
System.IO.Directory.CreateDirectory(fullUpLoadPath);
}
filePath = GetMapPath(filePath);
if (System.IO.File.Exists(filePath))
System.IO.File.Delete(filePath);
System.IO.FileStream fs = System.IO.File.Create(filePath);
fs.Close();
wb.Save(filePath);
}
