public static Cells SetFontUnderline(this Cells cells, FontUnderlineType value)
{
var style = new Style();
style.Font.Underline = value;
cells.ApplyStyle(style, new StyleFlag {
FontUnderline = true
});
return(cells);
}
public static Cells SetFontSize(this Cells cells, int value = Settings.FontSize)
{
var style = new Style();
style.Font.Size = value;
cells.ApplyStyle(style, new StyleFlag {
FontSize = true
});
return(cells);
}
public static Cells SetFontBold(this Cells cells, bool value = true)
{
var style = new Style();
style.Font.IsBold = value;
cells.ApplyStyle(style, new StyleFlag {
FontBold = true
});
return(cells);
}
public static Cells SetFontColor(this Cells cells, Color value)
{
var style = new Style();
style.Font.Color = value;
cells.ApplyStyle(style, new StyleFlag {
FontColor = true
});
return(cells);
}
public static Cells SetFontName(this Cells cells, string value = Settings.FontName)
{
var style = new Style();
style.Font.Name = value;
cells.ApplyStyle(style, new StyleFlag {
FontName = true
});
return(cells);
}
public static Cells SetVerticalAlignment(this Cells cells, TextAlignmentType value)
{
var style = new Style();
style.VerticalAlignment = value;
cells.ApplyStyle(style, new StyleFlag {
VerticalAlignment = true
});
return(cells);
}
public static Cells SetTextWrapped(this Cells cells, bool value = true)
{
var style = new Style();
style.IsTextWrapped = value;
cells.ApplyStyle(style, new StyleFlag {
WrapText = true
});
return(cells);
}
public static Cells SetFormat(this Cells cells, string value, params object[] args)
{
var style = new Style();
style.Custom = string.Format(value, args);
cells.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(cells);
}
public static Cells SetFormat(this Cells cells, int value)
{
var style = new Style();
style.Number = value;
cells.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(cells);
}
public static Cells SetForegroundColor(this Cells cells, Color value)
{
var style = new Style();
style.ForegroundColor = value;
style.Pattern = BackgroundType.Solid;
cells.ApplyStyle(style, new StyleFlag {
CellShading = true
});
return(cells);
}
/// <summary>
/// 导出数据 自定义样式
/// </summary>
/// <param name="tb_main"></param>
/// <param name="tb_detail"></param>
/// <param name="path"></param>
public void WriteToExcelStyle(DataTable tb_main, DataTable tb_detail, string path)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
sheet.Name = tb_main.TableName;
Cells cells = sheet.Cells;//单元格
cells.SetRowHeight(0, 24);
cells.SetRowHeight(1, 22);
int Colnum = tb_detail.Columns.Count; //表格列数
int Rownum = tb_detail.Rows.Count; //表格行数
//全局样式
Style main_style = workbook.CreateStyle();
main_style.Name = "main_style";
main_style.Font.Name = "微软雅黑";
main_style.Font.Size = 11;
cells.ApplyStyle(main_style, new StyleFlag()
{
All = true
});
//标题
cells.Merge(0, 0, 4, tb_detail.Columns.Count);
cells[0, 0].PutValue(tb_main.Rows[0]["title"]);
Style title_style = cells[0, 0].GetStyle();
title_style.VerticalAlignment = TextAlignmentType.Center;
title_style.HorizontalAlignment = TextAlignmentType.Left;
title_style.Font.Size = 12;
title_style.Font.IsBold = false;
title_style.Pattern = BackgroundType.Solid;
title_style.IsTextWrapped = true;
title_style.ForegroundColor = System.Drawing.Color.FromArgb(215, 215, 215);
title_style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
title_style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
title_style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
title_style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cells[0, 0].GetMergedRange().SetStyle(title_style);
//生成列名
for (int i = 0; i < tb_detail.Columns.Count; i++)
{
Cell cell = cells[4, i];
Style cell_style = cell.GetStyle();
cell_style.Font.Size = 11;
cell_style.HorizontalAlignment = TextAlignmentType.Left;
cell_style.TextDirection = TextDirectionType.LeftToRight;
cell_style.VerticalAlignment = TextAlignmentType.Center;
cell_style.Pattern = BackgroundType.Solid;
cell_style.IsTextWrapped = true;
cell_style.ForegroundColor = System.Drawing.Color.FromArgb(215, 215, 215);
cell_style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cell.SetStyle(cell_style, true);
cell.PutValue(tb_detail.Columns[i]);
cells.SetColumnWidth(i, 20);
}
int first_row = -1;
string item_name = "";
//生成数据行
for (int i = 1; i <= Rownum; i++)
{
cells.SetRowHeight(i + 4, 15);
for (int k = 0; k < Colnum; k++)
{
Cell cell = cells[i + 4, k];
Style cell_style = cell.GetStyle();
cell_style.Font.Name = "微软雅黑";
cell_style.Font.Size = 11;
cell_style.HorizontalAlignment = TextAlignmentType.Left;
cell_style.TextDirection = TextDirectionType.LeftToRight;
cell_style.VerticalAlignment = TextAlignmentType.Center;
cell_style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
cell_style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cell.SetStyle(cell_style);
cell.PutValue(tb_detail.Rows[i - 1][k].ToString());
}
if (first_row < 0)
{
first_row = 4 + 1;
item_name = tb_detail.Rows[i - 1][0].ToString();
}
else
{
if (!item_name.Equals(tb_detail.Rows[i - 1][0].ToString()))
{
cells.Merge(first_row, 7, i + 1 - first_row, 1);
cells.Merge(first_row, 6, i + 1 - first_row, 1);
first_row = i + 1;
item_name = tb_detail.Rows[i - 1][0].ToString();
}
else
{
if (i == Rownum)
{
cells.Merge(first_row, 7, i + 2 - first_row, 1);
cells.Merge(first_row, 6, i + 2 - first_row, 1);
first_row = -1;
item_name = "";
}
}
}
}
workbook.Save(path);
}
