/// <summary>
/// 获取自定义颜色位置
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
/// <returns></returns>
private static short GetCustomColor(this HSSFWorkbook workBook, string rgb)
{
SetOriginalRGB();
short indexed = defaultColorIndexed;
if (string.IsNullOrEmpty(rgb))
{
return(indexed);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(indexed);
}
byte red = 0;
byte green = 0;
byte blue = 0;
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(indexed);
}
HSSFPalette pattern = workBook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor hssfColor = pattern.FindColor(red, green, blue);
if (hssfColor == null)
{
return(pattern.SetCustomColor(rgb, -1));
}
indexed = hssfColor.Indexed;
return(indexed);
}
private short GetXLColour(System.Drawing.Color SystemColour)
{
HSSFPalette XlPalette = document.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
XlColour = XlColour ?? XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
return(XlColour.Indexed);
}
private static short GetXlColour(HSSFWorkbook workbook, Color systemColor)
{
short s = 0;
HSSFPalette xlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor xlColour = xlPalette.FindColor(systemColor.R, systemColor.G, systemColor.B);
if (xlColour == null)
{
xlColour = xlPalette.FindSimilarColor(systemColor.R, systemColor.G, systemColor.B);
s = xlColour.Indexed;
}
else
{
s = xlColour.Indexed;
}
return(s);
}
private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
s = XlColour.Indexed;
}
}
else
{
s = XlColour.Indexed;
}
return(s);
}
private void button1_Click(object sender, EventArgs e)
{
string fileName;
//fileName = System.IO.Path.GetDirectoryName(sourceFileTexBox.Text) + "\\";
fileName = "list_" + System.IO.Path.GetFileName(sourceFileTexBox.Text);
fileName += ".xls";
SaveFileDialog saveFileDia = new SaveFileDialog();
saveFileDia.Filter = "Excel文件|*.xls";
saveFileDia.FileName = fileName;
DialogResult result = saveFileDia.ShowDialog();
if (DialogResult.OK == result)
{
fileName = saveFileDia.FileName;
if (File.Exists(fileName))
{
MessageBox.Show("目标文件已经存在！", "错误");
return;
}
try
{
IWorkbook wb = new HSSFWorkbook();
ISheet tb = wb.CreateSheet(System.IO.Path.GetFileName(sourceFileTexBox.Text));
tb.DisplayGridlines = false;
tb.CreateRow(5).CreateCell(29).SetCellValue("備考");
tb.GetRow(5).CreateCell(3).SetCellValue("No.");
tb.GetRow(5).CreateCell(5).SetCellValue("関数名");
tb.GetRow(5).CreateCell(17).SetCellValue("テスト方法");
ICellStyle bg = (HSSFCellStyle)wb.CreateCellStyle();
IFont ft = wb.CreateFont();
ft.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
bg.SetFont(ft);
HSSFWorkbook wob = new HSSFWorkbook();
HSSFPalette pa = wob.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = pa.FindSimilarColor(23, 55, 93);
bg.FillForegroundColor = XlColour.Indexed; //15000;
bg.FillPattern = FillPattern.SolidForeground;
tb.CreateRow(3);
for (int i = 0; i < 50; i++)
{
tb.GetRow(3).CreateCell(i).CellStyle = bg;
}
tb.GetRow(3).GetCell(1).SetCellValue(System.IO.Path.GetFileName(sourceFileTexBox.Text));
ICellStyle Border3 = (HSSFCellStyle)wb.CreateCellStyle();
Border3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
tb.GetRow(5).GetCell(3).CellStyle = Border3;
ICellStyle Border2 = (HSSFCellStyle)wb.CreateCellStyle();
Border2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border4 = (HSSFCellStyle)wb.CreateCellStyle();
Border4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border5 = (HSSFCellStyle)wb.CreateCellStyle();
Border5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border6 = (HSSFCellStyle)wb.CreateCellStyle();
Border6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border7 = (HSSFCellStyle)wb.CreateCellStyle();
Border7.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border7.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border7.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border8 = (HSSFCellStyle)wb.CreateCellStyle();
Border8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border9 = (HSSFCellStyle)wb.CreateCellStyle();
Border9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border10 = (HSSFCellStyle)wb.CreateCellStyle();
Border10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border11 = (HSSFCellStyle)wb.CreateCellStyle();
Border11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border12 = (HSSFCellStyle)wb.CreateCellStyle();
Border12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
tb.GetRow(5).CreateCell(4).CellStyle = Border2;
tb.GetRow(5).GetCell(5).CellStyle = Border4;
for (int i = 6; i < 17; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).GetCell(17).CellStyle = Border4;
for (int i = 18; i < 29; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).GetCell(29).CellStyle = Border4;
for (int i = 30; i < 41; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).CreateCell(41).CellStyle = Border5;
ICellStyle Border1 = (HSSFCellStyle)wb.CreateCellStyle();
Border1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
int lastline = 6;
for (int i = 0; i < funcListBox.SelectedItems.Count; i++)
{
tb.CreateRow(6 + i).CreateCell(5).SetCellValue(funcListBox.SelectedItems[i].ToString());
tb.GetRow(6 + i).CreateCell(3).SetCellValue(" " + (i + 1).ToString());
tb.GetRow(6 + i).GetCell(3).CellStyle = Border6;
tb.GetRow(6 + i).CreateCell(4).CellStyle = Border1;
tb.GetRow(6 + i).GetCell(5).CellStyle = Border7;
for (int j = 6; j < 17; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(17).CellStyle = Border7;
for (int j = 18; j < 29; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(29).CellStyle = Border7;
for (int j = 30; j < 41; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(41).CellStyle = Border8;
lastline += 1;
}
tb.CreateRow(lastline).CreateCell(3).CellStyle = Border9;
tb.GetRow(lastline).CreateCell(4).CellStyle = Border10;
tb.GetRow(lastline).CreateCell(5).CellStyle = Border11;
for (int j = 6; j < 17; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(17).CellStyle = Border11;
for (int j = 18; j < 29; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(29).CellStyle = Border11;
for (int j = 30; j < 41; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(41).CellStyle = Border12;
for (int i = 0; i < 50; i++)
{
tb.SetColumnWidth(i, 1024);
}
using (FileStream fs = File.OpenWrite(fileName))
{
wb.Write(fs);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("生成成功！", "成功");
}
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontname">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <returns></returns>
public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, NPOI.HSSF.Util.HSSFColor fontcolor, int fontsize)
{
IFont font1 = hssfworkbook.CreateFont();
if (string.IsNullOrEmpty(fontfamily))
{
font1.FontName = fontfamily;
}
if (fontcolor != null)
{
font1.Color = fontcolor.Indexed;
}
font1.IsItalic = true;
font1.FontHeightInPoints = (short)fontsize;
return(font1);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <param name="showBorder">是否显示边框</param>
/// <returns></returns>
private static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font, FillPattern fillPattern, NPOI.HSSF.Util.HSSFColor fillForegroundColor, NPOI.HSSF.Util.HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va, bool showBorder)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.FillForegroundColor = fillForegroundColor.Indexed;
}
if (fillBackgroundColor != null)
{
cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed;
}
if (font != null)
{
cellstyle.SetFont(font);
}
if (showBorder)
{
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellstyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellstyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellstyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
}
return(cellstyle);
}
