public void SetToStyle(XSSFCellStyle style)
{
style.SetFillForegroundColor(BackgroundColor);
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderTop;
style.SetTopBorderColor(BorderTopColor);
style.BorderRight = BorderRight;
style.SetRightBorderColor(BorderRightColor);
style.BorderBottom = BorderBottom;
style.SetBottomBorderColor(BorderBottomColor);
style.BorderLeft = BorderLeft;
style.SetLeftBorderColor(BorderLeftColor);
style.VerticalAlignment = VerticalAlignment;
style.Alignment = HorizontalAlignment;
switch (_writingMode)
{
case WritingModeEnum.lr_tb:
style.Rotation = 0;
break;
case WritingModeEnum.tb_rl:
style.Rotation = -90;
break;
case WritingModeEnum.tb_lr:
style.Rotation = 90;
break;
default:
throw new ArgumentOutOfRangeException($"Writing mode {_writingMode} is not supported");
}
}
public void setCellColor(byte red, byte green, byte blue)
{
cellRgb[0] = red;
cellRgb[1] = green;
cellRgb[2] = blue;
defaultCellStyle.SetFillForegroundColor(new XSSFColor(cellRgb));
}
private void button5_Click(object sender, EventArgs e)
{
if (job.CurrentIndex != job.Count)
{
Comment com = new Comment();
com.ShowDialog();
string txt = com.CommentText;
XSSFWorkbook workbook = ExcelDataTableUtil.LoadExcel(job.JobPath);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(job.CurrentIndex);
row.Cells[dt.Columns.Count - 2].SetCellValue("U");
XSSFColor XlColour = new XSSFColor(button5.BackColor);
XSSFCellStyle cellstyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
row.Cells[dt.Columns.Count - 2].CellStyle = cellstyle;
row.Cells[dt.Columns.Count - 1].SetCellValue(txt);
ExcelDataTableUtil.WriteExcel(workbook, job.JobPath);
job.CurrentIndex++;
job.HasFinished++;
Judge();
}
else
{
MessageBox.Show("已经是最后一条了");
}
}
private void ApplyFill(ICellStyle cellStyle, CellStyle style)
{
if (style.FillPatternStyle.HasValue)
{
cellStyle.FillPattern = this.GetFillPattern(style.FillPatternStyle.Value);
}
XSSFCellStyle xssfStyle = null;
if (style.FillPatternColor.HasValue || style.BackgroundColor.HasValue)
{
xssfStyle = cellStyle as XSSFCellStyle;
}
if (style.FillPatternColor.HasValue)
{
var color = style.FillPatternColor.Value;
xssfStyle.SetFillForegroundColor(new XSSFColor(new[] { color.Red, color.Green, color.Blue }));
}
if (style.BackgroundColor.HasValue)
{
var color = style.BackgroundColor.Value;
xssfStyle.SetFillBackgroundColor(new XSSFColor(new[] { color.Red, color.Green, color.Blue }));
}
}
private void CreateStyles()
{
_excelStyle = new Dictionary <string, XSSFCellStyle>();
IFont titleFont = Getfont(18, new XSSFColor(new byte[] { 255, 255, 255 }));
IFont normalFont = Getfont(10, new XSSFColor(new byte[] { 39, 51, 89 }));
IFont leaveFont = Getfont(10, new XSSFColor(new byte[] { 255, 255, 255 }));
IFont notWorkDayFont = Getfont(10, new XSSFColor(new byte[] { 255, 0, 0 }));
XSSFCellStyle style = _wb.CreateCellStyle() as XSSFCellStyle;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFillForegroundColor(new XSSFColor(new byte[] { 63, 81, 181 }));
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(titleFont);
_excelStyle["title"] = style;
XSSFColor workDayBorderColor = new XSSFColor(new byte[] { 39, 51, 89 });
XSSFColor foreGroundColor = new XSSFColor(new byte[] { 255, 255, 255 });
XSSFColor notWorkDayBorderColor = new XSSFColor(new byte[] { 255, 0, 0 });
_excelStyle["workday"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["notWorkDay"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Thin);
style = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
style.Alignment = HorizontalAlignment.Left;
_excelStyle["employe"] = style;
_excelStyle["workdayMonthEnd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Double);
_excelStyle["notWorkDayMonthEnd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Double);
foreGroundColor = new XSSFColor(new byte[] { 239, 243, 255 });
_excelStyle["workdayOdd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["notWorkDayOdd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Thin);
style = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
style.Alignment = HorizontalAlignment.Left;
_excelStyle["employeOdd"] = style;
_excelStyle["workdayMonthEndOdd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Double);
_excelStyle["notWorkDayMonthEndOdd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Double);
foreGroundColor = new XSSFColor(new byte[] { 63, 81, 181 });
IDataFormat format = _wb.CreateDataFormat();
_excelStyle["leave"] = GetCellCommonStyle(leaveFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["leave"].DataFormat = format.GetFormat(";;;");
_excelStyle["leave"].FillPattern = FillPattern.BigSpots;
}
public static XSSFSheet ChangeColor(XSSFSheet sheet, int x, int y, Color color, XSSFCellStyle cellstyle)
{
XSSFRow row = (XSSFRow)sheet.GetRow(x);
XSSFCell cell = (XSSFCell)row.GetCell(y);
XSSFColor XlColour = new XSSFColor(color);
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cell.CellStyle = cellstyle;
return(sheet);
}
private XSSFCellStyle GetNormalRowStyleHeader(XSSFWorkbook hssfworkbook)
{
if (normalRowStyleHeader == null)
{
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 8;
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
font.IsBold = true;
font.FontName = "Verdana";
//byte[] rgb = new byte[3] { 192, 0, 0 };
byte[] rgb = new byte[3] {
0, 68, 132
};
//XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
//normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(System.Drawing.Color.Azure));
//normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(System.Drawing.Color.Black));
//CellStyle style = workbook.createCellStyle();
//style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
//style.setFillPattern(CellStyle.SOLID_FOREGROUND);
XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(rgb));
normalRowStyleHeaders.FillPattern = FillPattern.SolidForeground;
//normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(rgb));
//normalRowStyleHeaders.setFillPattern(XSSFCellStyle.);
//normalRowStyleHeaders.setFill;
normalRowStyleHeaders.Alignment = HorizontalAlignment.Center;
normalRowStyleHeaders.VerticalAlignment = VerticalAlignment.Center;
normalRowStyleHeaders.SetFont(font);
//XSSFCellStyle HeaderCellStyle1 = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
//normalRowStyleHeader.SetFillForegroundColor(new XSSFColor(rgb));
//normalRowStyleHeader.bac = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
//normalRowStyle.LeftBorderColor =
normalRowStyleHeaders.BorderTop = BorderStyle.None;
normalRowStyleHeaders.BorderRight = BorderStyle.None;
normalRowStyleHeaders.BorderBottom = BorderStyle.None;
normalRowStyleHeaders.BorderLeft = BorderStyle.None;
normalRowStyleHeader = normalRowStyleHeaders;
}
return(normalRowStyleHeader);
}
public void SetToStyle(XSSFCellStyle style)
{
style.SetFillForegroundColor(BackgroundColor);
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderTop;
style.SetTopBorderColor(BorderTopColor);
style.BorderRight = BorderRight;
style.SetRightBorderColor(BorderRightColor);
style.BorderBottom = BorderBottom;
style.SetBottomBorderColor(BorderBottomColor);
style.BorderLeft = BorderLeft;
style.SetLeftBorderColor(BorderLeftColor);
style.VerticalAlignment = VerticalAlignment;
style.Alignment = HorizontalAlignment;
}
public void SaveFileInSpecifiedPathXLSX(DataTable dt, string[] columns
, string fullPath)
{
using (FileStream stream = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
string[] headers = columns;
int columnsCount = headers.Count();
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Details");
ICreationHelper cH = wb.GetCreationHelper();
#region Cellstyle with bold and background color
XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
XSSFColor color = new XSSFColor(System.Drawing.Color.Lime);
((XSSFCellStyle)cellStyle).SetFillBackgroundColor(color);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(color);
XSSFFont yourFont = (XSSFFont)wb.CreateFont();
yourFont.FontHeightInPoints = (short)10.5;
yourFont.FontName = "Arial";
yourFont.IsBold = true;
yourFont.IsItalic = false;
cellStyle.SetFont(yourFont);
#endregion
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < columns.Length; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(columns[i]);
cell.CellStyle = cellStyle;
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < columnsCount; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i - 1].ItemArray[j].ToString()));
}
}
wb.Write(stream);
}
}
/// <summary>
/// Get a specific cell style
/// </summary>
/// <param name="workbook">Workbook</param>
/// <param name="reportStyle">ReportStyleCell</param>
/// <returns>XSSFCellStyle</returns>
private XSSFCellStyle GetStyle(IWorkbook workbook, ReportStyleCell reportStyle)
{
ICreationHelper creationHelper = workbook.GetCreationHelper();
//Set up column style
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
if (reportStyle == null)
{
return(cellStyle);
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)reportStyle.FontWeight;
//DataFormat
if (!string.IsNullOrEmpty(reportStyle.DataFormat))
{
cellStyle.SetDataFormat(creationHelper.CreateDataFormat().GetFormat(reportStyle.DataFormat));
cellStyle.Alignment = reportStyle.HorizontalAlignment;
}
//BackgroundColor
if (!string.IsNullOrEmpty(reportStyle.BackgroundColor))
{
XSSFColor backgroundColor = new XSSFColor(Color.FromName(reportStyle.BackgroundColor));
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(backgroundColor);
}
//Foreground Color
if (!string.IsNullOrEmpty(reportStyle.FontColor))
{
XSSFColor foregroundColor = new XSSFColor(Color.FromName(reportStyle.FontColor));
font.SetColor(foregroundColor);
}
//Wrap Text
cellStyle.SetFont(font);
cellStyle.WrapText = reportStyle.WrapText;
return(cellStyle);
}
private XSSFCellStyle GetCellCommonStyle(IFont font, XSSFColor color, XSSFColor foregroundColor, BorderStyle borderRight)
{
XSSFCellStyle style = _wb.CreateCellStyle() as XSSFCellStyle;
style.SetFillForegroundColor(foregroundColor);
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetRightBorderColor(color);
style.BorderRight = borderRight;
style.SetLeftBorderColor(color);
style.BorderLeft = BorderStyle.Thin;
style.SetBottomBorderColor(color);
style.BorderBottom = BorderStyle.Thin;
style.SetTopBorderColor(color);
style.BorderTop = BorderStyle.Thin;
style.SetFont(font);
return(style);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="xssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(XSSFWorkbook xssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
XSSFCellStyle cellstyle = xssfworkbook.CreateCellStyle() as XSSFCellStyle;
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.SetFillForegroundColor(fillForegroundColor);
}
if (fillBackgroundColor != null)
{
cellstyle.SetFillBackgroundColor(fillBackgroundColor);
}
if (font != null)
{
cellstyle.SetFont(font);
}
return(cellstyle);
}
public void FillReg17SpreadSheet(Dictionary <string, Reg17Record> records)
{
xssfwb = new XSSFWorkbook();
XSSFFont myFont = (XSSFFont)xssfwb.CreateFont();
myFont.FontHeightInPoints = 12;
myFont.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont2 = (XSSFFont)xssfwb.CreateFont();
myFont2.FontHeightInPoints = 18;
myFont2.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
IFont boldFont = xssfwb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var color = new XSSFColor(new byte[] { 196, 215, 155 });
XSSFCellStyle borderedCellStyle = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
XSSFCellStyle borderedCellStyle2 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle2.SetFont(myFont2);
borderedCellStyle2.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle2.Alignment = HorizontalAlignment.Center;
borderedCellStyle2.SetFillForegroundColor(color);
borderedCellStyle2.SetFillBackgroundColor(color);
borderedCellStyle2.FillBackgroundXSSFColor = color;
borderedCellStyle2.FillForegroundXSSFColor = color;
borderedCellStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle borderedCellStyle3 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle3.SetFont(myFont);
borderedCellStyle3.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle3.Alignment = HorizontalAlignment.Center;
borderedCellStyle3.SetFillForegroundColor(color);
borderedCellStyle3.SetFillBackgroundColor(color);
borderedCellStyle3.FillBackgroundXSSFColor = color;
borderedCellStyle3.FillForegroundXSSFColor = color;
borderedCellStyle3.FillPattern = FillPattern.SolidForeground;
borderedCellStyle3.WrapText = true;
ISheet Sheet = xssfwb.CreateSheet("Report");
Sheet.SetColumnWidth(0, 22 * 256);
Sheet.SetColumnWidth(1, 17 * 256);
Sheet.SetColumnWidth(2, 25 * 256);
Sheet.SetColumnWidth(3, 15 * 256);
Sheet.SetColumnWidth(4, 96 * 256);
Sheet.SetColumnWidth(5, 35 * 256);
//Creat The Headers of the excel
IRow row1 = Sheet.CreateRow(0);
//styling
ICellStyle boldStyle = xssfwb.CreateCellStyle();
boldStyle.SetFont(boldFont);
//Create The Actual Cells
row1.CreateCell(0).SetCellValue("Sistema PCI DSS");
row1.GetCell(0).CellStyle = boldStyle;
IRow row2 = Sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row3 = Sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("Informe de Aplicación de Parches");
row3.GetCell(0).CellStyle = borderedCellStyle2;
var cra = new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 5);
Sheet.AddMergedRegion(cra);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row4 = Sheet.CreateRow(4);
row4.CreateCell(0).SetCellValue("Servidores");
row4.GetCell(0).CellStyle = borderedCellStyle;
row4.CreateCell(1).SetCellValue("SQLPCI");
row4.GetCell(1).CellStyle = boldStyle;
row4.CreateCell(2).SetCellValue("Fecha de Aplicación");
row4.GetCell(2).CellStyle = borderedCellStyle;
row4.CreateCell(3).SetCellValue(DateTime.Now.ToString());
row4.GetCell(3).CellStyle = borderedCellStyle;
IRow row5 = Sheet.CreateRow(5);
row5.CreateCell(1).SetCellValue("SRVFILE");
row5.GetCell(1).CellStyle = boldStyle;
IRow row6 = Sheet.CreateRow(6);
row6.CreateCell(1).SetCellValue("Site-Transfer");
row6.GetCell(1).CellStyle = boldStyle;
IRow row7 = Sheet.CreateRow(7);
row7.CreateCell(1).SetCellValue("WEBPCI");
row7.GetCell(1).CellStyle = boldStyle;
IRow row8 = Sheet.CreateRow(8);
row8.CreateCell(1).SetCellValue("Proxy Inverso");
row8.GetCell(1).CellStyle = boldStyle;
IRow row9 = Sheet.CreateRow(9);
row9.CreateCell(1).SetCellValue("Proxy Servicios");
row9.GetCell(1).CellStyle = boldStyle;
IRow row10 = Sheet.CreateRow(10);
row10.CreateCell(1).SetCellValue("Proxy Web");
row10.GetCell(1).CellStyle = boldStyle;
IRow row11 = Sheet.CreateRow(11);
row11.CreateCell(1).SetCellValue("Parches");
row11.GetCell(1).CellStyle = boldStyle;
IRow row12 = Sheet.CreateRow(12);
row12.CreateCell(1).SetCellValue("ADMPCI");
row12.GetCell(1).CellStyle = boldStyle;
IRow row13 = Sheet.CreateRow(13);
row13.CreateCell(1).SetCellValue("File Gateway");
row13.GetCell(1).CellStyle = boldStyle;
IRow row14 = Sheet.CreateRow(14);
row14.CreateCell(1).SetCellValue("Volume Gateway");
row14.GetCell(1).CellStyle = boldStyle;
IRow row15 = Sheet.CreateRow(15);
row15.CreateCell(1).SetCellValue("RD Gateway");
row15.GetCell(1).CellStyle = boldStyle;
IRow row16 = Sheet.CreateRow(16);
row16.CreateCell(1).SetCellValue("Data Analysis");
row16.GetCell(1).CellStyle = boldStyle;
IRow row18 = Sheet.CreateRow(18);
row18.CreateCell(0).SetCellValue("Código Parche");
row18.GetCell(0).CellStyle = borderedCellStyle3;
row18.CreateCell(1).SetCellValue("Fecha Publicación");
row18.GetCell(1).CellStyle = borderedCellStyle3;
row18.CreateCell(2).SetCellValue("Producto");
row18.GetCell(2).CellStyle = borderedCellStyle3;
row18.CreateCell(3).SetCellValue("Clasificación");
row18.GetCell(3).CellStyle = borderedCellStyle3;
row18.CreateCell(4).SetCellValue("Mejoras y Correcciones");
row18.GetCell(4).CellStyle = borderedCellStyle3;
row18.CreateCell(5).SetCellValue("Opinión Impacto del Parche");
row18.GetCell(5).CellStyle = borderedCellStyle3;
XSSFFont myFont4 = (XSSFFont)xssfwb.CreateFont();
myFont4.FontHeightInPoints = 10;
//myFont4.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont5 = (XSSFFont)xssfwb.CreateFont();
myFont5.FontHeightInPoints = 8.5;
//myFont5.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
XSSFCellStyle borderedCellStyle4 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle4.SetFont(myFont4);
borderedCellStyle4.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle4.Alignment = HorizontalAlignment.Center;
XSSFCellStyle borderedCellStyle5 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle5.SetFont(myFont5);
borderedCellStyle5.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle5.Alignment = HorizontalAlignment.Center;
borderedCellStyle5.WrapText = true;
XSSFCellStyle borderedCellStyle6 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle6.SetFont(myFont4);
borderedCellStyle6.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle6.Alignment = HorizontalAlignment.Center;
borderedCellStyle6.WrapText = true;
int i = 19;
//List<Reg17Record> records = new List<Reg17Record>();
foreach (KeyValuePair <string, Reg17Record> entry in records)
{
// do something with entry.Value or entry.Key
Reg17Record record = (Reg17Record)entry.Value;
IRow rowi = Sheet.CreateRow(i);
rowi.CreateCell(0).SetCellValue(record.PatchCode);
rowi.GetCell(0).CellStyle = borderedCellStyle4;
rowi.CreateCell(1).SetCellValue(record.PublicationDate);
rowi.GetCell(1).CellStyle = borderedCellStyle4;
rowi.CreateCell(2).SetCellValue(record.Product);
rowi.GetCell(2).CellStyle = borderedCellStyle4;
rowi.CreateCell(3).SetCellValue(record.Classification);
rowi.GetCell(3).CellStyle = borderedCellStyle4;
rowi.CreateCell(4).SetCellValue(record.EnhancementsAndCorrections);
rowi.GetCell(4).CellStyle = borderedCellStyle5;
rowi.CreateCell(5).SetCellValue(record.ImpactOpinion);
rowi.GetCell(5).CellStyle = borderedCellStyle6;
i++;
}
}
/// <summary>
/// check_in匯出Excel
/// </summary>
/// <param name="xlsList"></param>
/// <param name="wk"></param>
/// <param name="sht"></param>
/// <param name="Title"></param>
public void ExportExcel(List <CheckRecordViewModel> xlsList, XSSFWorkbook wk, ISheet sht, string Title)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)wk.CreateCellStyle();
var TopRow = sht.CreateRow(0);
cellStyle = CreateCellData(wk, cellStyle, HorizontalAlignment.Center, VerticalAlignment.Center, 12);
byte[] colorRgb = { (byte)255, (byte)232, (byte)42 };
var myColor = new XSSFColor(colorRgb);
cellStyle.SetFillForegroundColor(myColor);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.BorderTop = BorderStyle.Medium;
cellStyle.BorderLeft = BorderStyle.Medium;
cellStyle.BorderRight = BorderStyle.Medium;
cellStyle.BorderBottom = BorderStyle.Medium;
SetCellData(TopRow, 0, Title, cellStyle);
SetCellData(TopRow, 1, " ", cellStyle);
SetCellData(TopRow, 2, " ", cellStyle);
SetCellData(TopRow, 3, " ", cellStyle);
SetCellData(TopRow, 4, " ", cellStyle);
//----------------------------------------------------------------------
var headerRow = sht.CreateRow(1);
XSSFCellStyle cell3C = (XSSFCellStyle)wk.CreateCellStyle();
cell3C = CreateCellData(wk, cell3C, HorizontalAlignment.Center, VerticalAlignment.Center, 12);
cell3C.SetFillForegroundColor(myColor);
cell3C.FillPattern = FillPattern.SolidForeground;
cell3C.BorderTop = BorderStyle.Medium;
cell3C.BorderLeft = BorderStyle.Medium;
cell3C.BorderRight = BorderStyle.Medium;
cell3C.BorderBottom = BorderStyle.Medium;
SetCellData(headerRow, 0, "日期", cell3C);
SetCellData(headerRow, 1, "星期", cell3C);
SetCellData(headerRow, 2, "上班打卡", cell3C);
SetCellData(headerRow, 3, "下班打卡", cell3C);
SetCellData(headerRow, 4, "備註", cell3C);
//----------------------------------------------------------------------
//---------------------------------------------------------------------- (Optional) set the width of the columns
sht.SetColumnWidth(0, 24 * 256);
sht.SetColumnWidth(1, 24 * 256);
sht.SetColumnWidth(2, 24 * 256);
sht.SetColumnWidth(3, 24 * 256);
sht.SetColumnWidth(4, 24 * 256);
//---------------------------------------------------------------------- Set the column names in the header row
var rowNumber = 2;
foreach (var exp in xlsList)
{
XSSFCellStyle cellStyle22 = (XSSFCellStyle)wk.CreateCellStyle();
cellStyle22 = CreateCellData(wk, cellStyle22, HorizontalAlignment.Center, VerticalAlignment.Center, 10);
cellStyle22.BorderTop = BorderStyle.Medium;
cellStyle22.BorderLeft = BorderStyle.Medium;
cellStyle22.BorderRight = BorderStyle.Medium;
cellStyle22.BorderBottom = BorderStyle.Medium;
int R = 0;
int G = 0;
int B = 0;
switch (exp.ci_da.DayOfWeek)
{
case DayOfWeek.Sunday:
R = 240;
G = 121;
B = 159;
break;
case DayOfWeek.Monday:
R = 255;
G = 255;
B = 255;
break;
case DayOfWeek.Tuesday:
R = 255;
G = 255;
B = 255;
break;
case DayOfWeek.Wednesday:
R = 255;
G = 255;
B = 255;
break;
case DayOfWeek.Thursday:
R = 255;
G = 255;
B = 255;
break;
case DayOfWeek.Friday:
R = 255;
G = 255;
B = 255;
break;
case DayOfWeek.Saturday:
R = 240;
G = 121;
B = 159;
break;
default:
break;
}
byte[] colorcell = { (byte)R, (byte)G, (byte)B };
var colorData = new XSSFColor(colorcell);
cellStyle22.SetFillForegroundColor(colorData);
cellStyle22.FillPattern = FillPattern.SolidForeground;
//--------------------------------
var row = sht.CreateRow(rowNumber++);
SetCellData(row, 0, exp.ci_da.ToString("yyyy/MM/dd"), cellStyle22);
SetCellData(row, 1, exp.ci_da.ToString("ddd"), cellStyle22);
if (exp.ci_ut.Ticks != 0)
{
SetCellData(row, 2, exp.ci_ut.ToString(), cellStyle22);
}
else
{
SetCellData(row, 2, "-", cellStyle22);
}
if (exp.ci_dt.Ticks != 0)
{
SetCellData(row, 3, exp.ci_dt.ToString(), cellStyle22);
}
else
{
SetCellData(row, 3, "-", cellStyle22);
}
SetCellData(row, 4, exp.Remark, cellStyle22);
}
}
public void UpdateLoadSheetFinishedEncodings(List <AudioFileEncoding> finishedEncodings)
{
if (finishedEncodings.Count == 0)
{
ConsolePrintHelpers.PrintYellowText("\n\n\n AODLoadsheet Is Current...");
return;
}
finishedEncodings = finishedEncodings.OrderBy(encoding => encoding.LSR.LoadSheetRowNumber).ToList();
ConsolePrintHelpers.PrintYellowText("\n\n\n [DO NOT CLOSE CONSOLE WINDOW] Updating AODLoadsheet Rows. Range: ");
ConsolePrintHelpers.PrintWhiteText((finishedEncodings.First().LSR.LoadSheetRowNumber + 1).ToString() + " thru " + (finishedEncodings.Last().LSR.LoadSheetRowNumber + 1).ToString() + " ► ");
int cursorLeft = Console.CursorLeft;
Console.WriteLine("\n\n");
Console.CursorTop -= 3;
IWorkbook workbook = ReadWorkBook();
ISheet sheet = workbook.GetSheet(_Config.AODLoadSheetWorkSheetName);
IRow row;
ICell cell;
XSSFCellStyle green = (XSSFCellStyle)workbook.CreateCellStyle();
byte[] rgb = new byte[3] {
0, 176, 80
};
green.SetFillForegroundColor(new XSSFColor(rgb));
green.FillPattern = FillPattern.SolidForeground;
green.BorderTop = BorderStyle.Thin;
green.BorderBottom = BorderStyle.Thin;
green.BorderLeft = BorderStyle.Thin;
green.BorderRight = BorderStyle.Thin;
green.Alignment = HorizontalAlignment.Left;
green.VerticalAlignment = VerticalAlignment.Center;
ICellStyle yellow = workbook.CreateCellStyle();
yellow.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
yellow.FillPattern = FillPattern.SolidForeground;
yellow.BorderTop = BorderStyle.Thin;
yellow.BorderBottom = BorderStyle.Thin;
yellow.BorderLeft = BorderStyle.Thin;
yellow.BorderRight = BorderStyle.Thin;
yellow.Alignment = HorizontalAlignment.Left;
yellow.VerticalAlignment = VerticalAlignment.Center;
ICellStyle red = workbook.CreateCellStyle();
red.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
red.FillPattern = FillPattern.SolidForeground;
red.BorderTop = BorderStyle.Thin;
red.BorderBottom = BorderStyle.Thin;
red.BorderLeft = BorderStyle.Thin;
red.BorderRight = BorderStyle.Thin;
red.Alignment = HorizontalAlignment.Left;
red.VerticalAlignment = VerticalAlignment.Center;
ICellStyle blue = workbook.CreateCellStyle();
blue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
blue.FillPattern = FillPattern.SolidForeground;
blue.BorderTop = BorderStyle.Thin;
blue.BorderBottom = BorderStyle.Thin;
blue.BorderLeft = BorderStyle.Thin;
blue.BorderRight = BorderStyle.Thin;
blue.Alignment = HorizontalAlignment.Left;
blue.VerticalAlignment = VerticalAlignment.Center;
foreach (AudioFileEncoding finishedEncoding in finishedEncodings)
{
AODLoadSheetRow lsr = finishedEncoding.LSR;
row = sheet.GetRow(lsr.LoadSheetRowNumber) ?? sheet.CreateRow(lsr.LoadSheetRowNumber);
if ((lsr.SuggestedAudioFile.AccuracyType == AccuracyType.Accurate || lsr.IsApproved) && (lsr.EncodingResult == Globals.Encoded || lsr.EncodingResult == Globals.Copied))
{
foreach (int colNum in _Config.AODLoadSheetColumnNumsWrite)
{
cell = row.GetCell(colNum) ?? row.CreateCell(colNum);
if (colNum == _Config.AODLoadSheetColumnNumsWrite[0])
{
cell.SetCellValue(lsr.SuggestedAudioFile.FullPath); cell.CellStyle = green;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[1])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyScore); cell.CellStyle = green;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[2])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyNote);
if (lsr.SuggestedAudioFile.AccuracyNote != Globals.HistoricalApprovalNote)
{
cell.CellStyle = green;
}
else
{
cell.CellStyle = blue;
}
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[3])
{
cell.SetCellValue(lsr.EncodingResult); cell.CellStyle = green;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[4])
{
cell.SetCellValue(lsr.DestinationPath); cell.CellStyle = green;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[5])
{
cell.SetCellValue(lsr.EncodedFileDurationPretty); cell.CellStyle = green;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[6])
{
cell.SetCellValue(lsr.EncodedFileDurationSeconds); cell.CellStyle = green;
}
}
}
else if (lsr.SuggestedAudioFile.AccuracyType == AccuracyType.Questionable && lsr.EncodingResult == Globals.Skipped)
{
foreach (int colNum in _Config.AODLoadSheetColumnNumsWrite)
{
cell = row.GetCell(colNum) ?? row.CreateCell(colNum);
if (colNum == _Config.AODLoadSheetColumnNumsWrite[0])
{
cell.SetCellValue(lsr.SuggestedAudioFile.FullPath); cell.CellStyle = yellow;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[1])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyScore); cell.CellStyle = yellow;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[2])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyNote);
if (lsr.SuggestedAudioFile.AccuracyNote != Globals.HistoricalApprovalNote)
{
cell.CellStyle = yellow;
}
else
{
cell.CellStyle = blue;
}
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[3])
{
cell.SetCellValue(lsr.EncodingResult); cell.CellStyle = yellow;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[4])
{
cell.SetCellValue(lsr.DestinationPath); cell.CellStyle = yellow;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[5])
{
cell.SetCellValue(Globals.Skipped); cell.CellStyle = yellow;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[6])
{
cell.SetCellValue(Globals.Skipped); cell.CellStyle = yellow;
}
}
}
else //AccuracyType.HighRisk or EncodingResult == "Failed: ..."
{
foreach (int colNum in _Config.AODLoadSheetColumnNumsWrite)
{
cell = row.GetCell(colNum) ?? row.CreateCell(colNum);
if (colNum == _Config.AODLoadSheetColumnNumsWrite[0])
{
cell.SetCellValue(lsr.SuggestedAudioFile.FullPath); cell.CellStyle = red;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[1])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyScore); cell.CellStyle = red;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[2])
{
cell.SetCellValue(lsr.SuggestedAudioFile.AccuracyNote);
if (lsr.SuggestedAudioFile.AccuracyNote != Globals.HistoricalApprovalNote)
{
cell.CellStyle = red;
}
else
{
cell.CellStyle = blue;
}
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[3])
{
cell.SetCellValue(lsr.EncodingResult); cell.CellStyle = red;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[4])
{
cell.SetCellValue(lsr.DestinationPath); cell.CellStyle = red;
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[5])
{
if (lsr.EncodingResult == Globals.Skipped)
{
cell.SetCellValue(Globals.Skipped); cell.CellStyle = red;
}
else
{
cell.SetCellValue(Globals.Failed); cell.CellStyle = red;
}
}
else if (colNum == _Config.AODLoadSheetColumnNumsWrite[6])
{
if (lsr.EncodingResult == Globals.Skipped)
{
cell.SetCellValue(Globals.Skipped); cell.CellStyle = red;
}
else
{
cell.SetCellValue(Globals.Failed); cell.CellStyle = red;
}
}
}
}
}
WriteWorkbook(workbook, cursorLeft);
}
/// <summary>
/// 輸出員工出勤記錄表Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public static Stream RA01ToExcel(DataTable SourceTable)
{
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
IRow headerRow = (XSSFRow)sheet.CreateRow(0);
//定義超過五分鐘後的顏色
XSSFCellStyle LateStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.Color = IndexedColors.DarkRed.Index;
LateStyle.SetFont(font);
//定義超過遲到 五分鐘內的顏色
XSSFCellStyle LateStyle5Min = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font2 = workbook.CreateFont() as XSSFFont;
font2.Color = IndexedColors.Green.Index;
LateStyle5Min.SetFont(font2);
//定義曠職顏色
XSSFCellStyle AbsStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColor = new XSSFColor();
BgColor.SetRgb(new byte[] { 255, 255, 0 });
AbsStyle.SetFillForegroundColor(BgColor);
AbsStyle.FillPattern = FillPattern.SolidForeground;
AbsStyle.SetFillForegroundColor(BgColor);
//定義輸出欄位
List <DataTableDisplay> DisplayList = new List <DataTableDisplay>();
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeNo",
DisplayName = "員工編號"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "DepartMentName",
DisplayName = "部門"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeName",
DisplayName = "姓名"
});
//2016.11.12 Scott新增英文名欄位
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeEName",
DisplayName = "英文名"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CompanyName",
DisplayName = "公司別"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "WorkDay",
DisplayName = "日期"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "StartWorkTime",
DisplayName = "上班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EndWorkTime",
DisplayName = "下班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "StartWorkOvertime",
DisplayName = "加班上班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EndWorkOvertime",
DisplayName = "加班下班"
});
// 1051129 Scott 將出勤描述 分為上午、下午、加班 3欄
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescM",
DisplayName = "出勤描述(上午)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescN",
DisplayName = "出勤描述(下午)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescOT",
DisplayName = "出勤描述(加班)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "LateMinFormat",
DisplayName = "遲到"
});
// Scott 105.12.27 移除備註欄為
//DisplayList.Add(new DataTableDisplay()
//{
// ColumnName = "Memo",
// DisplayName = "備註"
//});
for (int i = 0; i < DisplayList.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(DisplayList[i].DisplayName);
}
int IntRow = 1;
if (SourceTable.Rows.Count > 0)
{
string BrEmpNo = SourceTable.Rows[0]["EmployeeNo"].ToString();
string Test = "";
for (int i = 0; i < SourceTable.Rows.Count; i++)
{
if (BrEmpNo != SourceTable.Rows[i]["EmployeeNo"].ToString())
{
//換行!!
IRow tmpDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)tmpDataRow.CreateCell(0);
IntRow++;
BrEmpNo = SourceTable.Rows[i]["EmployeeNo"].ToString();
}
IRow dataRow = (XSSFRow)sheet.CreateRow(IntRow);
for (int j = 0; j < DisplayList.Count; j++)
{
try
{
Test = SourceTable.Rows[i][DisplayList[j].ColumnName].ToString();
}
catch
{
//查無此欄位 則空著!!
XSSFCell CellR = (XSSFCell)dataRow.CreateCell(j);
CellR.SetCellValue("");
continue;
}
//** 人事部稽核用與Web輸出不同!!! **
//若是正常打卡且有出差,則出差兩字拿掉
/*
* if (DisplayList[j].ColumnName == "AttendanceDescM" && SourceTable.Rows[i]["AttendanceDescM"].ToString().IndexOf("出差") != -1)
* {
*
* if ((SourceTable.Rows[i]["StartWorkTime"].ToString() != "" && SourceTable.Rows[i]["EndWorkTime"].ToString() != ""))
* {
* //正常上班 正常下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* else if ((SourceTable.Rows[i]["StartWorkTime"].ToString() != "" && SourceTable.Rows[i]["EndWorkOvertime"].ToString() != ""))
* {
* //正常上班 加班下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* else if ((SourceTable.Rows[i]["StartWorkOvertime"].ToString() != "" && SourceTable.Rows[i]["EndWorkOvertime"].ToString() != ""))
* {
* //加班上班 加班下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* }
*/
//員工
XSSFCell Cell = (XSSFCell)dataRow.CreateCell(j);
Cell.SetCellValue(SourceTable.Rows[i][DisplayList[j].ColumnName].ToString());
//遲到分鐘數 顏色判定
if (DisplayList[j].ColumnName == "LateMinFormat")
{
switch (SourceTable.Rows[i]["LateMin"].ToString())
{
case "":; break;
case "0":; break;
case "1": Cell.CellStyle = LateStyle5Min;; break;
case "2": Cell.CellStyle = LateStyle5Min;; break;
case "3": Cell.CellStyle = LateStyle5Min;; break;
case "4": Cell.CellStyle = LateStyle5Min;; break;
default: Cell.CellStyle = LateStyle; break;
}
}
//曠職 顏色判定
if (DisplayList[j].ColumnName == "AttendanceDescM")
{
string Tmp = SourceTable.Rows[i]["AttendanceDescM"].ToString();
if (Tmp.IndexOf("曠職") != -1)
{
Cell.CellStyle = AbsStyle;
}
}
if (DisplayList[j].ColumnName == "AttendanceDescN")
{
string Tmp = SourceTable.Rows[i]["AttendanceDescN"].ToString();
if (Tmp.IndexOf("曠職") != -1)
{
Cell.CellStyle = AbsStyle;
}
}
}
IntRow++;
}
/*
* foreach (DataColumn column in SourceTable.Columns)
* {
* XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
* cell.SetCellValue(p.ToString() +"-"+row[column].ToString());
*
*
* switch(column.ColumnName.ToString())
* {
* case "LateMin":;break;
* }
*
*/
//加一筆空白行數
//dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName.ToString()+"-"+);
}
/*
* XSSFRow row = (XSSFRow)sheet.CreateRow(1);
*
*
* XSSFCell cell;
*
* for (int i = 0; i < title.Length; i++)
* {
*
* cell = (XSSFCell)row.CreateCell(i + 2);
* cell.CellStyle = LateStyle5Min;
* cell.SetCellValue(title[i]);
* }
*/
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 通过模板生成EXCEL
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataList">sheet集合</param>
/// <param name="temName">模板名称</param>
/// <returns></returns>
public static byte[] writeExcelToFile(XSSFWorkbook workbook, List <ExcelSheetModel> dataList, String temName)
{
// 列宽一个像素的固定值
//decimal pixCellConst = 31.94888178913738m;
// 行宽一个像素的固定值
//decimal pixRowConst = 15.15151515151515m;
String location = null;
XSSFRow row = null;
foreach (ExcelSheetModel dataSheet in dataList)
{
if (!string.IsNullOrEmpty(dataSheet.sheetType))
{
}
else
{
#region 自定义模式
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(dataSheet.sheetName);
// 自动更新公式
sheet.ForceFormulaRecalculation = true;
foreach (ExcelCellModel dataCell in dataSheet.dataList)
{
if (!string.IsNullOrEmpty(dataCell.location))
{
location = dataCell.location.ToUpper();
int dataNum = 0;
int dataRow = 0;
string rowStr = "";
string columnStr = "";
for (int i = 0; i < location.Length; i++)
{
var locationArr = location.ToCharArray();
if (locationArr[i] >= 48 && locationArr[i] <= 57)
{
columnStr += locationArr[i];
}
if (locationArr[i] >= 65 && locationArr[i] <= 90)
{
rowStr += locationArr[i];
}
}
for (int i = 0; i < rowStr.Length; i++)
{
var rowStrArr = rowStr.ToCharArray();
char ch = rowStrArr[rowStr.Length - i - 1];
dataNum = (int)(ch - 'A' + 1);
dataNum *= int.Parse(Math.Pow(26, i).ToString());
dataRow += dataNum;
}
dataCell.rowNum = int.Parse(columnStr) - 1;
dataCell.cellNum = dataRow - 1;
}
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->Strat");
XSSFCellStyle setBorder = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)workbook.CreateFont();
XSSFColor fontColor = null;
XSSFColor backColor = null;
if (row == null || row.RowNum != dataCell.rowNum)
{
row = (XSSFRow)sheet.GetRow(dataCell.rowNum);
}
XSSFCell cell = (XSSFCell)row.CreateCell(dataCell.cellNum);
// check合并单元格
if (dataCell.regionCell != null)
{
if (dataCell.regionCell.Length == 2)
{
if (dataCell.rowNum > dataCell.regionCell[0])
{
//logger.Debug("RegionCella结束行小于当前行");
}
else if (dataCell.cellNum > dataCell.regionCell[1])
{
//logger.Debug("RegionCella结束列小于当前列");
}
else
{
sheet.AddMergedRegion(
new CellRangeAddress(dataCell.rowNum, dataCell.regionCell[0],
dataCell.cellNum, dataCell.regionCell[1]));
}
}
else
{
//logger.Debug("RegionCell参数异常");
}
}
// check列宽
if (dataCell.cellWidth != 0)
{
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* int.Parse(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())).ToString()));
* //*/
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* Convert.ToInt32(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString()))));
* //*/
sheet.SetColumnWidth(dataCell.cellNum, dataCell.cellWidth);
}
// check行高
if (dataCell.cellHeight != 0)
{
row.Height = dataCell.cellHeight;
}
// check字体颜色
if (dataCell.cellColor != null)
{
if (dataCell.cellColor.Length == 3)
{
if (((dataCell.cellColor[0] >= 0) || (dataCell.cellColor[0] <= 255)) &&
((dataCell.cellColor[1] >= 0) || (dataCell.cellColor[1] <= 255)) &&
((dataCell.cellColor[2] >= 0) || (dataCell.cellColor[2] <= 255)))
{
fontColor = new XSSFColor(Color.FromArgb(dataCell.cellColor[0],
dataCell.cellColor[1], dataCell.cellColor[2]));
font.SetColor(fontColor);
}
}
}
// check背景色
if (dataCell.backColor != null)
{
if (dataCell.backColor.Length == 3)
{
if (((dataCell.backColor[0] >= 0) || (dataCell.backColor[0] <= 255)) &&
((dataCell.backColor[1] >= 0) || (dataCell.backColor[1] <= 255)) &&
((dataCell.backColor[2] >= 0) || (dataCell.backColor[2] <= 255)))
{
backColor = new XSSFColor(Color.FromArgb(dataCell.backColor[0],
dataCell.backColor[1], dataCell.backColor[2]));
setBorder.FillPattern = FillPattern.SolidForeground;
setBorder.SetFillForegroundColor(backColor);
}
}
}
// check边框
if (dataCell.borderLine != null)
{
if (dataCell.borderLine.Length == 4)
{
setBorder.BorderTop = dataCell.borderLine[0];
setBorder.BorderBottom = dataCell.borderLine[1];
setBorder.BorderLeft = dataCell.borderLine[2];
setBorder.BorderRight = dataCell.borderLine[3];
}
}
// check字体
if (!string.IsNullOrEmpty(dataCell.fontName))
{
font.FontName = dataCell.fontName;
}
else
{
font.FontName = "宋体";
}
// check字体大小
if (dataCell.fontSize != 0)
{
font.FontHeightInPoints = dataCell.fontSize;
}
else
{
font.FontHeightInPoints = (short)10;
}
// 字体是否加粗
if (dataCell.Boldweight)
{
font.IsBold = true;
}
// 是否自动换行
if (dataCell.wrapText)
{
setBorder.WrapText = true;
}
// 单元格的值
if (!string.IsNullOrEmpty(dataCell.cellValue))
{
cell.SetCellValue(dataCell.cellValue);
}
else
{
cell.SetCellValue("");
}
// 水平类型
setBorder.Alignment = dataCell.horizontalAlignment;
// 垂直类型
setBorder.VerticalAlignment = dataCell.verticalAlignment;
// 图片
if (dataCell.excelPictureModel != null)
{
int col2 = 0;
int row2 = 0;
byte[] arr = null;
if (dataCell.excelPictureModel.CodeType == PrintStructFlag.QRCODE)
{//二维码
MemoryStream pictureIS = QRCodeUtil.GetQRCode(dataCell.excelPictureModel.qrCode);
arr = new byte[pictureIS.Length];
pictureIS.Position = 0;
pictureIS.Read(arr, 0, (int)pictureIS.Length);
pictureIS.Close();
col2 = dataCell.excelPictureModel.endColNum + 1;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
else if (dataCell.excelPictureModel.CodeType == PrintStructFlag.BARCODE)
{ //一维码
BarCodeClass bc = new BarCodeClass();
Image image = bc.ZXCreateBarCode(dataCell.excelPictureModel.qrCode);
ImageConverter imgconv = new ImageConverter();
arr = (byte[])imgconv.ConvertTo(image, typeof(byte[]));
col2 = dataCell.excelPictureModel.endColNum + 4;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
int pIndex = workbook.AddPicture(arr, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, dataCell.cellNum,
dataCell.rowNum,
col2,
row2);
patriarch.CreatePicture(anchor, pIndex);
}
setBorder.SetFont(font);
cell.CellStyle = setBorder;
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->End");
}
#endregion
}
}
string filePath = System.Threading.Thread.GetDomain().BaseDirectory + "\\";
var new_tempFileName = "to_" + temName + ".xlsx";
FileStream files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.Create);
workbook.Write(files);
files.Close();
files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.OpenOrCreate);
byte[] bytes = new byte[files.Length];
files.Read(bytes, 0, bytes.Length);
files.Close();
workbook.Close();
return(bytes);
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="startAddress"></param>
/// <param name="dataTable"></param>
/// <param name="iWorkbook"></param>
public void FillDataFromTable(XSSFWorkbook iWorkbook, XSSFSheet sheet, string startAddress, DataTable dataTable)
{
XSSFCellStyle headerCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
CT_Color ctColor = new CT_Color();
ctColor.SetRgb(112, 173, 71);
XSSFColor xssfColor = new XSSFColor(ctColor);
headerCellStyle.SetFillBackgroundColor(xssfColor);
headerCellStyle.SetFillForegroundColor(xssfColor);
XSSFFont hssfFont = iWorkbook.CreateFont() as XSSFFont;
hssfFont.FontHeightInPoints = 10;
hssfFont.FontName = "宋体";
hssfFont.Boldweight = 700;
headerCellStyle.SetFont(hssfFont);
XSSFCellStyle contentCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
XSSFFont contentHssfFont = iWorkbook.CreateFont() as XSSFFont;
contentHssfFont.FontHeightInPoints = 10;
contentHssfFont.FontName = "宋体";
contentCellStyle.SetFont(contentHssfFont);
string rowIndexStr = string.Empty;
string cellIndexStr = string.Empty;
int cellIndex = 0;
for (int i = 0; i < startAddress.Length; i++)
{
int tempNum;
if (int.TryParse(startAddress[i].ToString(), out tempNum))
{
rowIndexStr += "" + tempNum;
}
else
{
cellIndexStr += "" + startAddress[i];
}
}
var rowIndex = Convert.ToInt32(rowIndexStr);
for (int i = cellIndexStr.Length - 1; i >= 0; i--)
{
if (i == cellIndexStr.Length - 1)
{
cellIndex += cellIndexStr[i] - 65;
}
else
{
cellIndex += (cellIndexStr[i] - 64) * 26;
}
}
cellIndex = 0;
//textBox1.Text += "\r\n 共有数据:" + _DataTable.Rows.Count;
int tempCellIndex = cellIndex;
try
{
//sheet分开包含表头
if (!ckbSheet.Checked)
{
rowIndex = sheet.LastRowNum;
if (rowIndex != 0)
{
rowIndex++;
}
}
//是否包含表头
XSSFRow excelRow;
XSSFCell excelCell;
if (ckbIsIncludeHeader.Checked && rowIndex <= 1)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (string.IsNullOrEmpty(dataColumn.ColumnName))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataColumn.ColumnName));
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
}
rowIndex++;
tempCellIndex = cellIndex;
}
//填充数据
foreach (DataRow dataRow in dataTable.Rows)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (dataRow[dataColumn] == DBNull.Value || string.IsNullOrEmpty(Convert.ToString(dataRow[dataColumn])))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = contentCellStyle;
tempCellIndex++;
continue;
}
if (dataRow[dataColumn] is decimal || dataRow[dataColumn] is int)
{
excelCell.SetCellType(CellType.Numeric);
excelCell.SetCellValue(Convert.ToDouble(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
tempCellIndex++;
}
tempCellIndex = cellIndex;
rowIndex++;
}
}
catch (Exception ex)
{
throw new Exception("行" + rowIndex + "列" + tempCellIndex + "_" + ex.Message, ex);
}
}
public FileResult ExportExcel()
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("UpassLog");
var headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Processtime");
headerRow.CreateCell(1).SetCellValue("Category (id)");
headerRow.CreateCell(2).SetCellValue("Event (id)");
headerRow.CreateCell(3).SetCellValue("ProgramID");
headerRow.CreateCell(4).SetCellValue("InstitutionID");
headerRow.CreateCell(5).SetCellValue("GUID");
headerRow.CreateCell(6).SetCellValue("TaskID");
headerRow.CreateCell(7).SetCellValue("StateID");
headerRow.CreateCell(8).SetCellValue("CardSerialNumber");
headerRow.CreateCell(9).SetCellValue("URI");
headerRow.CreateCell(10).SetCellValue("URIType");
headerRow.CreateCell(11).SetCellValue("ErrorID");
headerRow.CreateCell(12).SetCellValue("Info/Error Description");
headerRow.RowStyle = workbook.CreateCellStyle();
headerRow.RowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
sheet.CreateFreezePane(0, 1, 0, 1);
int rowNumber = 1;
var row = sheet.CreateRow(rowNumber++);
foreach (var datarow in RetrievedResult)
{
row = sheet.CreateRow(rowNumber++);
row.CreateCell(0).SetCellValue(datarow.ProcessDatetime.ToString("MM/dd/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture));
row.CreateCell(1).SetCellValue(String.Format("{0} ({1})", datarow.Category, datarow.CategoryID));
row.CreateCell(2).SetCellValue(String.Format("{0} ({1})", datarow.Event, datarow.EventID));
row.CreateCell(3).SetCellValue(datarow.ProgramID);
row.CreateCell(4).SetCellValue(datarow.InstitutionID);
row.CreateCell(5).SetCellValue(datarow.GUID);
row.CreateCell(6).SetCellValue(String.Format("{0}", datarow.TaskID));
row.CreateCell(7).SetCellValue(String.Format("{0}", datarow.StateID));
row.CreateCell(8).SetCellValue(datarow.CardSerialNumber);
row.CreateCell(9).SetCellValue(datarow.URI);
row.CreateCell(10).SetCellValue(datarow.URIType);
row.CreateCell(11).SetCellValue(datarow.ProcessErrorID);
row.CreateCell(12).SetCellValue(datarow.ProcessErrorDescr);
}
//XSSFColor foreColor = new XSSFColor(Color.Red);
XSSFColor backColor = new XSSFColor(Color.Yellow);
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.SetFillForegroundColor(backColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
for (int i = 0; i <= 12; i++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(i);
cell.CellStyle = style;
cell.CellStyle.SetFont(font);
}
for (int i = 0; i <= 12; i++)
{
sheet.AutoSizeColumn(i);
}
MemoryStream output = new MemoryStream();
workbook.Write(output);
return(File(output.ToArray(), "application/vnd.ms-excel", "UpassLog.xlsx"));
}
public void computeResult(string uniqueID, IDictionary <int, string> eventDict, XSSFWorkbook wb, string sheetName, List <string> responseList,
List <string> questionTypeList, IDictionary <string, string> uniqueAnswerDict,
List <string> questionList, List <List <string> > answerList, string resultType = "Normal")
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
int totalNumChoiceLength = 0;
if (resultType != "Percent")
{
XSSFCreationHelper createHelper = (XSSFCreationHelper)wb.GetCreationHelper();
}
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
//Group all the different answers list together
foreach (KeyValuePair <string, string> entry in uniqueAnswerDict)
{
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(entry.Value));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
int cellCount = 1;
List <string> answerChoiceList = JsonConvert.DeserializeObject <List <string> >(entry.Key);
if (answerChoiceList.Count > totalNumChoiceLength)
{
totalNumChoiceLength = answerChoiceList.Count;
}
foreach (string choice in answerChoiceList)
{
row.CreateCell(cellCount).SetCellValue(choice);
row.GetCell(cellCount).CellStyle = xStyle;
cellCount += 1;
}
rowCount += 1;
}
xStyle = boldFont(wb);
//xStyle.FillPattern = FillPattern.NoFill;
List <string> openEndedList = new List <string>();
Dictionary <int, List <string> > openEndedResponseDict = new Dictionary <int, List <string> >();
Dictionary <string, int> choiceResponseDict = new Dictionary <string, int>();
//Get a list of response
foreach (var response in responseList)
{
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
List <string> feedbackResponse = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
int qnNo = 1;
foreach (string feedback in feedbackResponse)
{
List <string> openEndedResponse = new List <string>();
if (questionTypeList[qnNo - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
if (openEndedResponseDict.ContainsKey(qnNo))
{
openEndedResponseDict[qnNo].Add(feedback);
}
else
{
openEndedResponse.Add(feedback);
openEndedResponseDict.Add(qnNo, openEndedResponse);
}
}
else if (questionTypeList[qnNo - 1] == "2") //Multiple Choice
{
if (choiceResponseDict.ContainsKey(qnNo + "||" + feedback))
{
choiceResponseDict[qnNo + "||" + feedback] += 1;
}
else
{
choiceResponseDict.Add(qnNo + "||" + feedback, 1);
}
}
qnNo += 1;
}
}
//Print out all the question number and text
int qnCount = 1;
foreach (var qnText in questionList)
{
if (questionTypeList[qnCount - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
openEndedList.Add("Q" + qnCount + "." + qnText);
}
else if (questionTypeList[qnCount - 1] == "2") //Multiple Choice
{
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue("Q" + qnCount + ". ");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(0 + totalNumChoiceLength + 1).SetCellValue(qnText);
xStyle = normalFont(wb);
color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
for (int i = 0; i < answerList[qnCount - 1].Count; i++)
{
//Print out all the question response
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][i]))
{
double displayValue = 0;
if (resultType == "Percent")
{
double value = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
displayValue = value / responseList.Count * 100;
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2) + "%");
}
else
{
displayValue = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2));
}
row.GetCell(1 + i).CellStyle = xStyle;
}
else
{
row.CreateCell(1 + i).SetCellValue(0);
row.GetCell(1 + i).CellStyle = xStyle;
}
xStyle.SetFillForegroundColor(color); //to end off the color xStyle
}
rowCount += 1;
}
qnCount++;
}
summarySheet.CreateRow(rowCount);
rowCount++;
xStyle = boldFont(wb);
//Print out all the openEnded Questions
foreach (var openEnded in openEndedList)
{
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue(openEnded);
row.GetCell(0).CellStyle = xStyle;
rowCount += 1;
int qnNo = Convert.ToInt32(openEnded.Split('.')[0].Remove(0, 1));
//Create rows for response answers
if (openEndedResponseDict.ContainsKey(qnNo))
{
foreach (var response in openEndedResponseDict[qnNo])
{
xStyle = normalFont(wb);
row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(1).SetCellValue(response);
row.GetCell(1).CellStyle = xStyle;
rowCount += 1;
}
}
else
{
rowCount += 1;
}
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
public void SendAttendanceReportToAdminHRManager()
{
var year = DateTime.Now.ToString("yyyy");
DateTime d = DateTime.Now;
d = d.AddMonths(-1);
int month = d.Month;
string monthName = new DateTime(2000, month, 1).ToString("MMM", CultureInfo.InvariantCulture);
var rootPath = @"C:\Temp\AttendanceReportToAdmin\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
if (!Directory.Exists(rootPath + year + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\");
}
if (!Directory.Exists(rootPath + year + "\\" + monthName + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\" + monthName + "\\");
}
int locationId = 0;
var SearchFor = "Month";
char c = '0';
var InputOne = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, c);
var InputTwo = DateTime.Now.Year.ToString();
bool status = true;
IList <AttendanceData> attendanceData = new List <AttendanceData>();
Attendance_Report Model = new Attendance_Report();
Model.sP_GetAttendanceCountReports = new List <SP_GetAttendanceCountReport>();
var SP_locationId = new SqlParameter("@locationId", locationId);
var SP_SelectType = new SqlParameter("@SelectType", SearchFor);
var SP_InputOne = new SqlParameter("@InputOne", InputOne);
var SP_InputTwo = new SqlParameter("@InputTwo", InputTwo);
var SP_Status = new SqlParameter("@Status", status);
string usp = "LMS.usp_GetAttendanceCountReport @locationId, @SelectType, @InputOne, @InputTwo,@Status";
Model.sP_GetAttendanceCountReports = _dbContext._sp_GetAttendanceCountReport.FromSql(usp, SP_locationId, SP_SelectType, SP_InputOne, SP_InputTwo, SP_Status).ToList();
string attendanceReportPath = @"C:\Temp\AttendanceReportToAdmin\" + year + "\\" + monthName + "\\" + "AttendanceReport.xlsx";
if (File.Exists(attendanceReportPath))
{
File.Delete(attendanceReportPath);
}
var memory = new MemoryStream();
using (var sw = new FileStream(attendanceReportPath, FileMode.Create, FileAccess.Write))
{
int i = 1;
IWorkbook workbook;
ICell cell;
workbook = new XSSFWorkbook();
ICellStyle headerStyle = workbook.CreateCellStyle();
ISheet sheet = workbook.CreateSheet("Attendance Report");
IRow row = sheet.CreateRow(0);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Aadyam Consultant llp.");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Employee Management System");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Attendance Report:-" + monthName + "/" + year);
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Code");
cell = row.CreateCell(1);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Name");
cell = row.CreateCell(2);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Working Days");
cell = row.CreateCell(3);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave Without Approval");
cell = row.CreateCell(4);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave With Approval");
cell = row.CreateCell(5);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Total Leaves");
cell = row.CreateCell(6);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.BorderRight = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Present Days");
byte[] rgb1 = new byte[3] {
242, 255, 204
};
byte[] rgb2 = new byte[3] {
230, 255, 204
};
byte[] rgb3 = new byte[3] {
230, 255, 255
};
byte[] rgb4 = new byte[3] {
255, 214, 204
};
byte[] rgb5 = new byte[3] {
214, 245, 214
};
byte[] rgb6 = new byte[3] {
255, 238, 204
};
byte[] rgb7 = new byte[3] {
217, 255, 179
};
XSSFCellStyle headerStyle1 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle1.BorderBottom = BorderStyle.Medium;
headerStyle1.BorderLeft = BorderStyle.Medium;
headerStyle1.SetFillForegroundColor(new XSSFColor(rgb1));
headerStyle1.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle2.BorderBottom = BorderStyle.Medium;
headerStyle2.BorderLeft = BorderStyle.Medium;
headerStyle2.SetFillForegroundColor(new XSSFColor(rgb2));
headerStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle3 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle3.BorderBottom = BorderStyle.Medium;
headerStyle3.BorderLeft = BorderStyle.Medium;
headerStyle3.SetVerticalAlignment(3);
headerStyle3.SetFillForegroundColor(new XSSFColor(rgb3));
headerStyle3.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle4 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle4.BorderBottom = BorderStyle.Medium;
headerStyle4.BorderLeft = BorderStyle.Medium;
headerStyle4.SetVerticalAlignment(3);
headerStyle4.SetFillForegroundColor(new XSSFColor(rgb4));
headerStyle4.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle5 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle5.BorderBottom = BorderStyle.Medium;
headerStyle5.BorderLeft = BorderStyle.Medium;
headerStyle5.SetFillForegroundColor(new XSSFColor(rgb5));
headerStyle5.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle6 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle6.BorderBottom = BorderStyle.Medium;
headerStyle6.BorderLeft = BorderStyle.Medium;
headerStyle6.SetFillForegroundColor(new XSSFColor(rgb6));
headerStyle6.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle7 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle7.BorderBottom = BorderStyle.Medium;
headerStyle7.BorderLeft = BorderStyle.Medium;
headerStyle7.BorderRight = BorderStyle.Medium;
headerStyle7.SetFillForegroundColor(new XSSFColor(rgb7));
headerStyle7.FillPattern = FillPattern.SolidForeground;
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 7000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 5600);
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3200);
foreach (var attendance in Model.sP_GetAttendanceCountReports)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = headerStyle1;
cell.SetCellValue(attendance.EmployeeCode);
cell = row.CreateCell(1);
cell.CellStyle = headerStyle2;
cell.SetCellValue(attendance.EmployeeName);
cell = row.CreateCell(2);
cell.CellStyle = headerStyle3;
cell.SetCellValue(Convert.ToInt32(attendance.WorkingDay));
cell = row.CreateCell(3);
cell.CellStyle = headerStyle4;
cell.SetCellValue(Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(4);
cell.CellStyle = headerStyle5;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave));
cell = row.CreateCell(5);
cell.CellStyle = headerStyle6;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave) + Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(6);
cell.CellStyle = headerStyle7;
cell.SetCellValue(Convert.ToInt32(attendance.PresentDays));
i++;
}
workbook.Write(sw);
workbook.Close();
}
var persons = _repository.Employee.getAdminHrManager()
.Select(p => new
{
EmailAddress = p.EmailAddress
}).ToList();
foreach (var p in persons)
{
string To = p.EmailAddress;
string subject = "EMS Monthly Attendance Report";
string body = "Dear Sir/Maa'm \n" +
"Kindly find monthly attendance report in attachment";
new EmailManager(_configuration, _repository).SendEmail(subject, body, To, attendanceReportPath);
}
}
/// <summary>
/// 輸出出勤資料Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public Stream RG01ToExcel(ReportFormModel ReportFrom)
{
List <RG01Model> RG01List = new List <RG01Model>();
string SQL = "SELECT *, ";
SQL += " CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat FROM gvOutgoing WHERE 1=1";
if (ReportFrom.Company != "")
{
SQL += " AND Company = @Company ";
}
if (ReportFrom.EmpNo != "")
{
SQL += " AND OutMan = @OutMan ";
}
if (ReportFrom.DepartMentNo != "")
{
SQL += " AND DepartMentNo = @DepartMentNo ";
}
SQL += " AND OutDate BETWEEN @StartDate AND @EndDate ";
SQL += " AND AlermTotal > 0 Order by OutMan,OutDate,OutTime";
DataTable dt = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("Company", DbType.String, ReportFrom.Company.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, ReportFrom.EmpNo.ToString()),
DataAccess.CreateParameter("DepartMentNo", DbType.String, ReportFrom.DepartMentNo.ToString()),
DataAccess.CreateParameter("StartDate", DbType.String, ReportFrom.StartDate.ToString().Replace("-", "")),
DataAccess.CreateParameter("EndDate", DbType.String, ReportFrom.EndDate.ToString().Replace("-", ""))
}
);
for (int i = 0; i < dt.Rows.Count; i++)
{
RG01Model tmpM = new RG01Model();
tmpM.OutId = dt.Rows[i]["OutId"].ToString();
tmpM.OutDate = dt.Rows[i]["OutDateFormat"].ToString();
tmpM.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
tmpM.OutTime = dt.Rows[i]["OutTimeFormat"].ToString();
tmpM.OutMan = dt.Rows[i]["OutMan"].ToString();
tmpM.Status = dt.Rows[i]["StatusDisplay"].ToString();
tmpM.CompanyName = dt.Rows[i]["CompanyName"].ToString();
tmpM.DepartMentName = dt.Rows[i]["DepartMentName"].ToString();
tmpM.AlermTotal = dt.Rows[i]["AlermTotal"].ToString();
tmpM.GoOutTime = dt.Rows[i]["GoOutTimeFormat"].ToString();
tmpM.CustomerName = dt.Rows[i]["CustomerName"].ToString();
tmpM.Location = dt.Rows[i]["Location"].ToString();
/*
* SELECT
* Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat
* FROM gvOutgoing
*/
SQL = "SELECT *, ";
SQL += " CASE Alerm WHEN 0 THEN '正常' WHEN 1 THEN '異常' END AS AlermDisplay,CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat ";
SQL += " FROM gvOutHistory WHERE OutId = @OutId AND OutMan = @OutMan Order by UpdateDate,UpdateTime";
DataTable DtDtl = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, tmpM.OutId),
DataAccess.CreateParameter("OutMan", DbType.String, tmpM.OutMan),
}
);
for (int j = 0; j < DtDtl.Rows.Count; j++)
{
RG01DtlModel tmpDtl = new RG01DtlModel();
tmpDtl.OutDate = DtDtl.Rows[j]["OutDateFormat"].ToString();
tmpDtl.OutTime = DtDtl.Rows[j]["OutTimeFormat"].ToString();
tmpDtl.CustomerName = DtDtl.Rows[j]["CustomerName"].ToString();
tmpDtl.GoOutTime = DtDtl.Rows[j]["GoOutTimeFormat"].ToString();
tmpDtl.RecordManName = DtDtl.Rows[j]["RecordManName"].ToString();
tmpDtl.Location = DtDtl.Rows[j]["Location"].ToString();
tmpDtl.Status = DtDtl.Rows[j]["StatusDisplay"].ToString();
tmpDtl.UpdateDate = DtDtl.Rows[j]["UpdateDateFormat"].ToString();
tmpDtl.UpdateTime = DtDtl.Rows[j]["UpdateTimeFormat"].ToString();
tmpDtl.Alerm = DtDtl.Rows[j]["AlermDisplay"].ToString();
tmpM.RG01Dtl.Add(tmpDtl);
}
RG01List.Add(tmpM);
}
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
//定義EmpInfo顏色
XSSFCellStyle EmpInfoStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColor = new XSSFColor();
BgColor.SetRgb(new byte[] { 102, 204, 255 });
EmpInfoStyle.SetFillForegroundColor(BgColor);
EmpInfoStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle HeaderStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorHeader = new XSSFColor();
BgColorHeader.SetRgb(new byte[] { 252, 213, 180 });
HeaderStyle.SetFillForegroundColor(BgColorHeader);
HeaderStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle BodyStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorBody = new XSSFColor();
BgColorBody.SetRgb(new byte[] { 216, 218, 188 });
BodyStyle.SetFillForegroundColor(BgColorBody);
BodyStyle.FillPattern = FillPattern.SolidForeground;
int IntRow = 0; //Excel 第幾列
string IntEmp = "";
for (int i = 0; i < RG01List.Count; i++)
{
if (IntEmp != RG01List[i].OutMan)
{
if (IntEmp != "")
{
//換行!!
IRow SpaceDataRowV = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCellV = (XSSFCell)SpaceDataRowV.CreateCell(0);
IntRow++;
}
//員工資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow EmpInfo = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell Cell0 = (XSSFCell)EmpInfo.CreateCell(0);
Cell0.SetCellValue("員工編號");
Cell0.CellStyle = EmpInfoStyle;
XSSFCell Cell1 = (XSSFCell)EmpInfo.CreateCell(1);
Cell1.SetCellValue("員工姓名");
Cell1.CellStyle = EmpInfoStyle;
XSSFCell Cell2 = (XSSFCell)EmpInfo.CreateCell(2);
Cell2.SetCellValue("公 司 別");
Cell2.CellStyle = EmpInfoStyle;
XSSFCell Cell3 = (XSSFCell)EmpInfo.CreateCell(3);
Cell3.SetCellValue("部 門");
Cell3.CellStyle = EmpInfoStyle;
XSSFCell Cell4 = (XSSFCell)EmpInfo.CreateCell(4);
Cell4.SetCellValue("異常次數");
Cell4.CellStyle = EmpInfoStyle;
IntRow++;
IRow EmpInfoContent = (XSSFRow)sheet.CreateRow(IntRow);
EmpInfoContent.CreateCell(0).SetCellValue(RG01List[i].OutMan);
EmpInfoContent.CreateCell(1).SetCellValue(RG01List[i].EmployeeName);
EmpInfoContent.CreateCell(2).SetCellValue(RG01List[i].CompanyName);
EmpInfoContent.CreateCell(3).SetCellValue(RG01List[i].DepartMentName);
int AlermCount = 0;
AlermCount = (from tmp in RG01List
where tmp.OutMan == RG01List[i].OutMan
select tmp).Count();
EmpInfoContent.CreateCell(4).SetCellValue(AlermCount);
IntRow++;
IntEmp = RG01List[i].OutMan;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊員工資料
}
//單頭資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow headerRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell headerCell2 = (XSSFCell)headerRow.CreateCell(2);
headerCell2.SetCellValue("外出日期");
headerCell2.CellStyle = HeaderStyle;
XSSFCell headerCell3 = (XSSFCell)headerRow.CreateCell(3);
headerCell3.SetCellValue("預計外出時間");
headerCell3.CellStyle = HeaderStyle;
XSSFCell headerCell4 = (XSSFCell)headerRow.CreateCell(4);
headerCell4.SetCellValue("會議時間");
headerCell4.CellStyle = HeaderStyle;
XSSFCell headerCell5 = (XSSFCell)headerRow.CreateCell(5);
headerCell5.SetCellValue("客戶名稱");
headerCell5.CellStyle = HeaderStyle;
XSSFCell headerCell6 = (XSSFCell)headerRow.CreateCell(6);
headerCell6.SetCellValue("地 點");
headerCell6.CellStyle = HeaderStyle;
XSSFCell headerCell7 = (XSSFCell)headerRow.CreateCell(7);
headerCell7.SetCellValue("單據狀態");
headerCell7.CellStyle = HeaderStyle;
IntRow++;
IRow headerContentRow = (XSSFRow)sheet.CreateRow(IntRow);
headerContentRow.CreateCell(2).SetCellValue(RG01List[i].OutDate);
headerContentRow.CreateCell(3).SetCellValue(RG01List[i].OutTime);
headerContentRow.CreateCell(4).SetCellValue(RG01List[i].GoOutTime);
headerContentRow.CreateCell(5).SetCellValue(RG01List[i].CustomerName);
headerContentRow.CreateCell(6).SetCellValue(RG01List[i].Location);
headerContentRow.CreateCell(7).SetCellValue(RG01List[i].Status);
IntRow++;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單頭資料
//單身資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow bodyRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell bodyCell4 = (XSSFCell)bodyRow.CreateCell(4);
bodyCell4.SetCellValue("項 次");
bodyCell4.CellStyle = BodyStyle;
XSSFCell bodyCell5 = (XSSFCell)bodyRow.CreateCell(5);
bodyCell5.SetCellValue("系統判定");
bodyCell5.CellStyle = BodyStyle;
XSSFCell bodyCell6 = (XSSFCell)bodyRow.CreateCell(6);
bodyCell6.SetCellValue("單據狀態");
bodyCell6.CellStyle = BodyStyle;
XSSFCell bodyCell7 = (XSSFCell)bodyRow.CreateCell(7);
bodyCell7.SetCellValue("外出日期");
bodyCell7.CellStyle = BodyStyle;
XSSFCell bodyCell8 = (XSSFCell)bodyRow.CreateCell(8);
bodyCell8.SetCellValue("外出時間");
bodyCell8.CellStyle = BodyStyle;
XSSFCell bodyCell9 = (XSSFCell)bodyRow.CreateCell(9);
bodyCell9.SetCellValue("會議時間");
bodyCell9.CellStyle = BodyStyle;
XSSFCell bodyCell10 = (XSSFCell)bodyRow.CreateCell(10);
bodyCell10.SetCellValue("客戶名稱");
bodyCell10.CellStyle = BodyStyle;
XSSFCell bodyCell11 = (XSSFCell)bodyRow.CreateCell(11);
bodyCell11.SetCellValue("地 點");
bodyCell11.CellStyle = BodyStyle;
XSSFCell bodyCell12 = (XSSFCell)bodyRow.CreateCell(12);
bodyCell12.SetCellValue("編輯時間");
bodyCell12.CellStyle = BodyStyle;
XSSFCell bodyCell13 = (XSSFCell)bodyRow.CreateCell(13);
bodyCell13.SetCellValue("編 輯 者");
bodyCell13.CellStyle = BodyStyle;
IntRow++;
for (int j = 0; j < RG01List[i].RG01Dtl.Count; j++)
{
IRow ItemRow = (XSSFRow)sheet.CreateRow(IntRow);
ItemRow.CreateCell(4).SetCellValue((j + 1).ToString());
ItemRow.CreateCell(5).SetCellValue(RG01List[i].RG01Dtl[j].Alerm);
ItemRow.CreateCell(6).SetCellValue(RG01List[i].RG01Dtl[j].Status);
ItemRow.CreateCell(7).SetCellValue(RG01List[i].RG01Dtl[j].OutDate);
ItemRow.CreateCell(8).SetCellValue(RG01List[i].RG01Dtl[j].OutTime);
ItemRow.CreateCell(9).SetCellValue(RG01List[i].RG01Dtl[j].GoOutTime);
ItemRow.CreateCell(10).SetCellValue(RG01List[i].RG01Dtl[j].CustomerName);
ItemRow.CreateCell(11).SetCellValue(RG01List[i].RG01Dtl[j].Location);
ItemRow.CreateCell(12).SetCellValue(RG01List[i].RG01Dtl[j].UpdateDate + " " + RG01List[i].RG01Dtl[j].UpdateTime);
ItemRow.CreateCell(13).SetCellValue(RG01List[i].RG01Dtl[j].RecordManName);
IntRow++;
}
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單身資料
//換行!!
IRow SpaceDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)SpaceDataRow.CreateCell(0);
IntRow++;
}
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
//XSSFWorkbook workbook = new XSSFWorkbook();(.xls用该格式限制列为256列）
//(.xlsx 16384列)
string sheetname = dtSource.TableName;
XSSFSheet sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)//超过6355就新建一个表
{
sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
}
#region 表头及样式
//{
// XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// HSSFFont font = workbook.CreateFont() as HSSFFont;
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
//}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.SetFillForegroundColor(new XSSFColor(new byte[] { 153, 204, 204 }));
headStyle.FillPattern = FillPattern.SolidForeground;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontName = "宋体";
font.FontHeightInPoints = 11;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 320);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
XSSFCellStyle rowStyle = workbook.CreateCellStyle() as XSSFCellStyle;
rowStyle.Alignment = HorizontalAlignment.Center;
XSSFFont cellfont = workbook.CreateFont() as XSSFFont;
cellfont.FontName = "宋体";
cellfont.FontHeightInPoints = 11;
rowStyle.SetFont(cellfont);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
newCell.CellStyle = rowStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
// ms.Flush();
//ms.Position = 0;
// workbook.Dispose();
return(ms);
}
}
public void TestThemesTableColors()
{
// Load our two test workbooks
XSSFWorkbook simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
XSSFWorkbook complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Save and re-load them, to check for stability across that
XSSFWorkbook simpleRS = XSSFTestDataSamples.WriteOutAndReadBack(simple) as XSSFWorkbook;
XSSFWorkbook complexRS = XSSFTestDataSamples.WriteOutAndReadBack(complex) as XSSFWorkbook;
// Fetch fresh copies to test with
simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Files and descriptions
Dictionary <String, XSSFWorkbook> workbooks = new Dictionary <String, XSSFWorkbook>();
workbooks.Add(testFileSimple, simple);
workbooks.Add("Re-Saved_" + testFileSimple, simpleRS);
workbooks.Add(testFileComplex, complex);
workbooks.Add("Re-Saved_" + testFileComplex, complexRS);
// Sanity check
//Assert.AreEqual(rgbExpected.Length, rgbExpected.Length);
// For offline testing
bool createFiles = false;
// Check each workbook in turn, and verify that the colours
// for the theme-applied cells in Column A are correct
foreach (String whatWorkbook in workbooks.Keys)
{
XSSFWorkbook workbook = workbooks[whatWorkbook];
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
int startRN = 0;
if (whatWorkbook.EndsWith(testFileComplex))
{
startRN++;
}
for (int rn = startRN; rn < rgbExpected.Length + startRN; rn++)
{
XSSFRow row = sheet.GetRow(rn) as XSSFRow;
Assert.IsNotNull(row, "Missing row " + rn + " in " + whatWorkbook);
String ref1 = (new CellReference(rn, 0)).FormatAsString();
XSSFCell cell = row.GetCell(0) as XSSFCell;
Assert.IsNotNull(cell,
"Missing cell " + ref1 + " in " + whatWorkbook);
int expectedThemeIdx = rn - startRN;
ThemeElement themeElem = ThemeElement.ById(expectedThemeIdx);
Assert.AreEqual(themeElem.name.ToLower(), cell.StringCellValue,
"Wrong theme at " + ref1 + " in " + whatWorkbook);
// Fonts are theme-based in their colours
XSSFFont font = (cell.CellStyle as XSSFCellStyle).GetFont();
CT_Color ctColor = font.GetCTFont().GetColorArray(0);
Assert.IsNotNull(ctColor);
Assert.AreEqual(true, ctColor.IsSetTheme());
Assert.AreEqual(themeElem.idx, ctColor.theme);
// Get the colour, via the theme
XSSFColor color = font.GetXSSFColor();
// Theme colours aren't tinted
Assert.AreEqual(color.HasTint, false);
// Check the RGB part (no tint)
Assert.AreEqual(rgbExpected[expectedThemeIdx], HexDump.EncodeHexString(color.RGB),
"Wrong theme colour " + themeElem.name + " on " + whatWorkbook);
long themeIdx = font.GetCTFont().GetColorArray(0).theme;
Assert.AreEqual(expectedThemeIdx, themeIdx,
"Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook
);
if (createFiles)
{
XSSFCellStyle cs = row.Sheet.Workbook.CreateCellStyle() as XSSFCellStyle;
cs.SetFillForegroundColor(color);
cs.FillPattern = FillPatternType.SolidForeground;
row.CreateCell(1).CellStyle = (cs);
}
}
if (createFiles)
{
FileStream fos = new FileStream("Generated_" + whatWorkbook, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fos);
fos.Close();
}
}
}
private static void workbookData(List <DataHolder> dict)
{
_numOfFarms = ConsolUtil.getNumberofFarms();
for (int row = 0; row < _rowLabels.Count; row++)
{
rowLabeler(row, dict);
}
int col = 1;
foreach (DataHolder b in dict)
{
ICell cell;
string stripper = b.Data_array.Substring(1, b.Data_array.Length - 2);
string[] sArray = stripper.Split(',');
{
XSSFCellStyle style = (XSSFCellStyle)_wb.CreateCellStyle();
XSSFFont font = (XSSFFont)_wb.CreateFont();
font.FontName = "Arial";
cell = _sheet.GetRow(0).CreateCell(col);
style.SetFillForegroundColor(new XSSFColor(Color.FromArgb(255, 242, 204)));
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Left;
try
{
cell.SetCellValue(ConsolUtil.GetFarmName(b.Branch_id));
}
catch
{
cell.SetCellValue("N/A");
}
font.FontHeightInPoints = 14;
style.SetFont(font);
cell.CellStyle = style;
}
for (int i = 0; i < sArray.Length; i++)
{
XSSFCellStyle style = (XSSFCellStyle)_wb.CreateCellStyle();
XSSFFont font = (XSSFFont)_wb.CreateFont();
cell = _sheet.GetRow(_dataCells[i]).CreateCell(col);
style.Alignment = HorizontalAlignment.Left;
ConsolUtil.InputDataToSheet(sArray[i], cell);
font.FontHeightInPoints = 11;
style.SetFont(font);
cell.CellStyle = style;
}
col++;
}
_sheet.AutoSizeColumn(1);
_sheet.AutoSizeColumn(2);
_sheet.AutoSizeColumn(1);
}
public void EmailSentToAllEmployee()
{
DateTime d = DateTime.Now;
if (d.Day == 1)
{
SendAttendanceReportToAdminHRManager();
d = d.AddMonths(-1);
int year = d.Year;
int month = d.Month;
string monthName = new DateTime(2000, month, 1).ToString("MMM", CultureInfo.InvariantCulture);
int TotalDays = DateTime.DaysInMonth(year, month);
var rootPath = @"C:\Temp\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
if (!Directory.Exists(rootPath + year + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\");
}
if (!Directory.Exists(rootPath + year + "\\" + monthName + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\" + monthName + "\\");
}
var results = _dbContext.Person.Include(x => x.Location).Include(x => x.Role).Where(x => x.Role.Name != "Admin" && x.Location.IsActive == true)
.Select(p => new
{
p.Id,
p.FullName,
p.EmployeeCode,
p.EmailAddress
}).ToList();
int SrId = 0;
foreach (var p in results)
{
string attendanceReportPath = @"C:\Temp\" + year + "\\" + monthName + "\\" + p.EmployeeCode + "AttendanceReport.xlsx";
if (File.Exists(attendanceReportPath))
{
File.Delete(attendanceReportPath);
}
string InputOne = year.ToString();
char c = '0';
string InputTwo = month.ToString().PadLeft(2, c);
List <EmployeeAttendanceData> data1 = new List <EmployeeAttendanceData>();
List <EmployeeAttendanceData> data = Data(SrId, "Month", p.EmployeeCode, InputOne, InputTwo, data1);
SrId = SrId + data.Count() + 4;
var memory = new MemoryStream();
using (var sw = new FileStream(attendanceReportPath, FileMode.Create, FileAccess.Write))
{
int i = 1;
byte[] rgb1 = new byte[3] {
255, 255, 204
};
byte[] rgb2 = new byte[3] {
214, 245, 214
};
byte[] rgb3 = new byte[3] {
255, 214, 204
};
byte[] rgb4 = new byte[3] {
255, 240, 179
};
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Attendance Report");
IRow row = sheet.CreateRow(0);
XSSFCellStyle headerStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.BorderRight = BorderStyle.Medium;
headerStyle.SetFillForegroundColor(new XSSFColor(rgb1));
headerStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle presentStyle = (XSSFCellStyle)workbook.CreateCellStyle();
presentStyle.BorderBottom = BorderStyle.Medium;
presentStyle.BorderLeft = BorderStyle.Medium;
presentStyle.BorderRight = BorderStyle.Medium;
presentStyle.SetFillForegroundColor(new XSSFColor(rgb2));
presentStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle absentStyle = (XSSFCellStyle)workbook.CreateCellStyle();
absentStyle.BorderBottom = BorderStyle.Medium;
absentStyle.BorderLeft = BorderStyle.Medium;
absentStyle.BorderRight = BorderStyle.Medium;
absentStyle.SetFillForegroundColor(new XSSFColor(rgb3));
absentStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
dateStyle.BorderBottom = BorderStyle.Medium;
dateStyle.BorderLeft = BorderStyle.Medium;
dateStyle.BorderRight = BorderStyle.Medium;
dateStyle.SetFillForegroundColor(new XSSFColor(rgb4));
dateStyle.FillPattern = FillPattern.SolidForeground;
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Employee Name:-");
row.CreateCell(1).SetCellValue(p.FullName);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Employee Code:-");
row.CreateCell(1).SetCellValue(p.EmployeeCode);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Monthly Attendance Report:-" + monthName + "/" + year);
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
ICell cell0 = row.CreateCell(0);
cell0.CellStyle = headerStyle;
cell0.SetCellValue("DATE");
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("STATUS");
cell1.CellStyle = headerStyle;
ICell cell2 = row.CreateCell(2);
cell2.CellStyle = headerStyle;
cell2.SetCellValue("TIME IN");
ICell cell3 = row.CreateCell(3);
cell3.CellStyle = headerStyle;
cell3.SetCellValue("TIME OUT");
ICell cell4 = row.CreateCell(4);
cell4.CellStyle = headerStyle;
cell4.SetCellValue("TOTAL HOURS");
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 4000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 4000);
sheet.SetColumnWidth(4, 4000);
foreach (var attendance in data)
{
row = sheet.CreateRow(i);
DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt");
if (attendance.Status == "Present")
{
ICell cell00 = row.CreateCell(0);
cell00.CellStyle = presentStyle;
cell00.SetCellValue(Convert.ToDateTime(attendance.DateIn).ToString("dd/MM/yyyy").ToString());
ICell cell01 = row.CreateCell(1);
cell01.SetCellValue(attendance.Status);
cell01.CellStyle = presentStyle;
ICell cell02 = row.CreateCell(2);
cell02.CellStyle = presentStyle;
cell02.SetCellValue(attendance.TimeIn == null ? "" : DateTime.Today.Add(attendance.TimeIn.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell03 = row.CreateCell(3);
cell03.CellStyle = presentStyle;
cell03.SetCellValue(attendance.TimeOut == null ? "" : DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell04 = row.CreateCell(4);
cell04.CellStyle = presentStyle;
cell04.SetCellValue(attendance.TotalHours == null ? "" : attendance.TotalHours.ToString());
}
else
{
ICell cell00 = row.CreateCell(0);
cell00.CellStyle = absentStyle;
cell00.SetCellValue(Convert.ToDateTime(attendance.DateIn).ToString("dd/MM/yyyy").ToString());
ICell cell01 = row.CreateCell(1);
cell01.SetCellValue(attendance.Status);
cell01.CellStyle = absentStyle;
ICell cell02 = row.CreateCell(2);
cell02.CellStyle = absentStyle;
cell02.SetCellValue(attendance.TimeIn == null ? "" : DateTime.Today.Add(attendance.TimeIn.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell03 = row.CreateCell(3);
cell03.CellStyle = absentStyle;
cell03.SetCellValue(attendance.TimeOut == null ? "" : DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell04 = row.CreateCell(4);
cell04.CellStyle = absentStyle;
cell04.SetCellValue(attendance.TotalHours == null ? "" : attendance.TotalHours.ToString());
}
i++;
}
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Total No of Days: -");
row.CreateCell(1).SetCellValue(TotalDays.ToString());
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("No of Days Present:-");
row.CreateCell(1).SetCellValue(data.Where(x => x.Status == "Present").Count().ToString());
workbook.Write(sw);
workbook.Close();
}
data = null;
using (var stream = new FileStream(attendanceReportPath, FileMode.Open))
{
stream.CopyToAsync(memory);
}
memory.Position = 0;
string To = p.EmailAddress;
string subject = "Monthly Attendance Report";
string body = "Dear " + p.FullName + "\n" +
"Kindly find monthly attendance report in attachment.\n" +
"Your Code Number: " + p.EmployeeCode + "\n" +
"User Name: " + p.EmailAddress;
new EmailManager(_configuration, _repository).SendEmail(subject, body, To, attendanceReportPath);
}
}
}
private void FlatFileExcel(string sourceConn, string sourceData, string sourceSQL, string destFile)
{
string dir = Path.GetDirectoryName(destFile);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SqlConnection srcConn = new SqlConnection(string.Format(sourceConn, sourceData)))
{
SqlCommand srcCmd = new SqlCommand(sourceSQL, srcConn);
srcCmd.CommandTimeout = CmdTimeout;
srcConn.Open();
using (FileStream stream = new FileStream(destFile, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
XSSFCellStyle headerStyle1 = (XSSFCellStyle)wb.CreateCellStyle();
XSSFFont headerFont1 = (XSSFFont)wb.CreateFont();
headerFont1.Color = XSSFFont.DEFAULT_FONT_COLOR;
headerFont1.IsBold = true;
headerStyle1.SetFont(headerFont1);
headerStyle1.SetFillForegroundColor(new XSSFColor(Color.LightGreen));
headerStyle1.FillPattern = FillPattern.SolidForeground;
headerStyle1.BorderRight = BorderStyle.Thin;
headerStyle1.BorderLeft = BorderStyle.Thin;
headerStyle1.BorderBottom = BorderStyle.Thin;
XSSFCellStyle normalStyle = (XSSFCellStyle)wb.CreateCellStyle();
XSSFFont normalFont = (XSSFFont)wb.CreateFont();
normalFont.Color = XSSFFont.DEFAULT_FONT_COLOR;
normalStyle.SetFont(normalFont);
normalStyle.BorderRight = BorderStyle.Thin;
normalStyle.BorderLeft = BorderStyle.Thin;
normalStyle.BorderBottom = BorderStyle.Thin;
normalStyle.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("#,##0.00"));
using (SqlDataReader reader = srcCmd.ExecuteReader())
{
int i = 0;
IRow row = sheet.CreateRow(0);
List <string> list = new List <string>();
for (; i < reader.FieldCount; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(reader.GetName(i));
cell.CellStyle = headerStyle1;
}
i = 1;
while (reader.Read())
{
IRow rowLoop = sheet.CreateRow(i);
for (int j = 0; j < reader.FieldCount; j++)
{
ICell cell = rowLoop.CreateCell(j);
cell.SetCellValue(reader[j].ToString());
cell.CellStyle = normalStyle;
}
i++;
}
for (i = 0; i < reader.FieldCount; i++)
{
sheet.AutoSizeColumn(i);
}
}
wb.Write(stream);
}
srcConn.Close();
}
}