}//解压结束
/// <summary>
/// 初始化导出表格样式
/// </summary>
/// LZ Add 2016-08-04
/// <param name="book">Excel文件</param>
/// <param name="param"></param>
/// <param name="paramComment">批注字典 注意:字典的key要与表头key一致</param>
/// <returns></returns>
public static ISheet CreateSheet(HSSFWorkbook book, Dictionary <string, int> param, Dictionary <string, string> paramComment, string sheetName = "Sheet1")
{
//添加一个sheet
ISheet sheet1 = book.CreateSheet(sheetName);
IRow row = sheet1.CreateRow(0);
//初始化样式
ICellStyle mStyle = book.CreateCellStyle();
mStyle.Alignment = HorizontalAlignment.Center;
mStyle.VerticalAlignment = VerticalAlignment.Center;
IFont mfont = book.CreateFont();
mfont.FontHeight = 10 * 20;
mStyle.SetFont(mfont);
HSSFPatriarch patr = sheet1.CreateDrawingPatriarch() as HSSFPatriarch;
NPOI.SS.UserModel.ICreationHelper facktory = book.GetCreationHelper();
HSSFComment comment = null;
NPOI.SS.UserModel.IClientAnchor anchor = facktory.CreateClientAnchor();
int i = 0;
foreach (var item in param)
{
//设置列宽
sheet1.SetColumnWidth(i, item.Value * 256);
sheet1.SetDefaultColumnStyle(i, mStyle);
row.CreateCell(i).SetCellValue(item.Key.ToString());
if (paramComment.ContainsKey(item.Key.ToString()))
{
//设置批注
anchor = facktory.CreateClientAnchor();
anchor.Col1 = row.GetCell(i).ColumnIndex;
anchor.Col2 = row.GetCell(i).ColumnIndex + 1;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = patr.CreateCellComment(anchor) as HSSFComment;
comment.String = new HSSFRichTextString(paramComment[item.Key.ToString()].ToString());
comment.Author = ("CySoft");
row.GetCell(i).CellComment = (comment);
}
i++;
}
i = 0;
sheet1.GetRow(0).Height = 28 * 20;
return(sheet1);
}
public void UpdateWriteExcelFileLog(ref List <int[]> yellowError)
{
int yellow_total = yellowError.Count;
using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
try
{
var ext = Path.GetExtension(fileName).ToLower();
IWorkbook wk;
if (ext.Contains(".xlsx"))
{
wk = new XSSFWorkbook(fs);
}
else
{
wk = new HSSFWorkbook(fs);
}
fs.Close();
ISheet sheet = wk.GetSheetAt(0);
IDrawing patr = sheet.CreateDrawingPatriarch();
NPOI.SS.UserModel.ICreationHelper facktory = wk.GetCreationHelper();
/**************************填写模板中设置背景色****************************/
foreach (int[] yellow in yellowError)
{
ICellStyle yellowStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle, ref yellowStyle);
// 背景色
yellowStyle.FillPattern = FillPattern.SolidForeground;
yellowStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowStyle;
}
FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
wk.Write(fileStream);
fileStream.Close();
}
catch (Exception e)
{
FileLog.WriteLog("" + e);
}
}
}
public void UpdateWriteExcelFileGP(string sheetname, ref List <int[]> redError, ref List <int[]> yellowError, ref List <int[]> blueError)
{
int red_total = redError.Count;
int yellow_total = yellowError.Count;
using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
try
{
var ext = Path.GetExtension(fileName).ToLower();
IWorkbook wk;
if (ext.Contains(".xlsx"))
{
wk = new XSSFWorkbook(fs);
}
else
{
wk = new HSSFWorkbook(fs);
}
fs.Close();
ISheet sheet = wk.GetSheet(sheetname);
IDrawing patr = sheet.CreateDrawingPatriarch();
NPOI.SS.UserModel.ICreationHelper facktory = wk.GetCreationHelper();
// 错误类型-批注
Dictionary <int, string> Dic_Comment = new Dictionary <int, string>();
Dic_Comment.Add(1, "未匹配上的省市名");
Dic_Comment.Add(2, "不支持的日期格式");
Dic_Comment.Add(3, "上报时间不能晚于当前时间");
Dic_Comment.Add(4, "当期时间与上次上报的时间格式不相同");
Dic_Comment.Add(5, "指标值包含中文");
Dic_Comment.Add(6, "指标值不是数值");
Dic_Comment.Add(7, "指标值越界");
Dic_Comment.Add(8, "带%的指标小于1");
Dic_Comment.Add(9, "分子÷分母不等于指标值");
Dic_Comment.Add(10, "无法填报");
Dictionary <int, int[]> Dic_Count = new Dictionary <int, int[]>();
Dic_Count.Add(1, new int[2] {
0, 1
});
Dic_Count.Add(2, new int[2] {
0, 1
});
Dic_Count.Add(3, new int[2] {
0, 1
});
Dic_Count.Add(4, new int[2] {
0, 1
});
Dic_Count.Add(5, new int[2] {
0, 0
});
Dic_Count.Add(6, new int[2] {
0, 1
});
Dic_Count.Add(7, new int[2] {
0, 1
});
Dic_Count.Add(8, new int[2] {
0, 0
});
Dic_Count.Add(9, new int[2] {
0, 0
});
Dic_Count.Add(10, new int[2] {
0, 2
});
// 背景色
ICellStyle yellowStyle = wk.CreateCellStyle();
yellowStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
yellowStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle redStyle = wk.CreateCellStyle();
redStyle.FillForegroundColor = HSSFColor.Red.Index;
redStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle blueStyle = wk.CreateCellStyle();
blueStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
blueStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle yellowErrorStyle = null, redErrorStyle = null, blueErrorStyle = null;
/**************************填写模板中设置背景色****************************/
foreach (int[] yellow in yellowError)
{
if (sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (yellowErrorStyle == null)
{
yellowErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle, ref yellowErrorStyle);
yellowErrorStyle.FillPattern = FillPattern.SolidForeground;
yellowErrorStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
}
sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowErrorStyle;
}
else
{
sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowStyle;
}
Dic_Count[yellow[2]][0]++;
}
foreach (int[] red in redError)
{
if (sheet.GetRow(red[0]).GetCell(red[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (redErrorStyle == null)
{
redErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(red[0]).GetCell(red[1]).CellStyle, ref redErrorStyle);
redErrorStyle.FillPattern = FillPattern.SolidForeground;
redErrorStyle.FillForegroundColor = HSSFColor.Red.Index;
}
sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redErrorStyle;
}
else
{
sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redStyle;
}
Dic_Count[red[2]][0]++;
}
foreach (int[] blue in blueError)
{
if (sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (blueErrorStyle == null)
{
blueErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle, ref blueErrorStyle);
blueErrorStyle.FillPattern = FillPattern.SolidForeground;
blueErrorStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
}
sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueErrorStyle;
}
else
{
sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueStyle;
}
Dic_Count[blue[2]][0]++;
}
/**************************问题数据明细****************************/
ISheet sheetLogInfo = null;
int index = wk.GetSheetIndex("问题数据明细");
if (index > -1)
{
wk.RemoveSheetAt(index);
}
sheetLogInfo = wk.CreateSheet("问题数据明细");
IRow headerRow = sheetLogInfo.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("位置");
headerRow.CreateCell(1).SetCellValue("描述");
headerRow.CreateCell(2).SetCellValue("备注");
int rowIndex = 1;
string address = null, value1 = "必改", value2 = "自查", value3 = "无法填报", sheetname2 = "#填写模板!";
foreach (int[] red in redError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(red[1]) + "" + (red[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[red[2]]);
dataRow.CreateCell(2).SetCellValue(value1);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = redStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
foreach (int[] yellow in yellowError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(yellow[1]) + "" + (yellow[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[yellow[2]]);
dataRow.CreateCell(2).SetCellValue(value2);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = yellowStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
foreach (int[] blue in blueError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(blue[1]) + "" + (blue[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[blue[2]]);
dataRow.CreateCell(2).SetCellValue(value3);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
/**************************问题数据统计****************************/
ISheet sheetLog = null;
index = wk.GetSheetIndex("问题数据统计");
if (index > -1)
{
wk.RemoveSheetAt(index);
}
sheetLog = wk.CreateSheet("问题数据统计");
headerRow = sheetLog.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("描述");
headerRow.CreateCell(1).SetCellValue("次数");
headerRow.CreateCell(2).SetCellValue("备注");
// handling value.
rowIndex = 1;
foreach (KeyValuePair <int, int[]> kv in Dic_Count)
{
IRow dataRow = sheetLog.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(Dic_Comment[kv.Key]);
dataRow.CreateCell(1).SetCellValue(kv.Value[0]);
dataRow.CreateCell(2).SetCellValue(kv.Value[1] == 1 ? value1 : (kv.Value[1] == 0 ? value2 : value3));
sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = kv.Value[1] == 1 ? redStyle : (kv.Value[1] == 0 ? yellowStyle : blueStyle);
rowIndex++;
}
IRow totalRow = sheetLog.CreateRow(rowIndex);
totalRow.CreateCell(0).SetCellValue("必改问题合计：" + red_total);
totalRow.CreateCell(1).SetCellValue("自查问题合计：" + yellow_total);
totalRow.CreateCell(2).SetCellValue("无法填报合计：" + blueError.Count);
sheetLog.GetRow(rowIndex).GetCell(0).CellStyle = redStyle;
sheetLog.GetRow(rowIndex).GetCell(1).CellStyle = yellowStyle;
sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle;
IRow remarkRow1 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow2 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow3 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow4 = sheetLog.CreateRow(++rowIndex);
remarkRow1.CreateCell(0).SetCellValue("注：");
remarkRow2.CreateCell(0).SetCellValue("【必改】问题数据必须修改后才能入库");
remarkRow3.CreateCell(0).SetCellValue("【自查】问题数据请自行核查数据，填报人员对数据准确性负责");
remarkRow4.CreateCell(0).SetCellValue("【无法填报】表示根据数据填报核减模版，该单元格指标为无法填报，该指标数据不入库");
//FileStream fileStream = File.OpenWrite(fileName);
FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
//FileStream fileStream = new FileStream(@"D:\\新建文件夹\\test.xlsx",FileMode.Create, FileAccess.Write);
wk.Write(fileStream);
fileStream.Close();
}
catch (Exception e)
{
FileLog.WriteLog("" + e);
}
}
}
