protected ushort GenerateHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle ReportHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ReportSubHeaderStyle) { NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)workSheet.CreateRow((int)RowPos).CreateCell(0); //workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex+5)); cell.SetCellValue("Payroll Allocation Report - Detail"); cell.CellStyle = ReportHeaderStyle; RowPos++; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); cell.SetCellValue("From"); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1); cell.SetCellValue(PeriodFrom.ToString("yyyy-MM-dd")); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2); cell.SetCellValue("To"); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3); cell.SetCellValue(PeriodTo.ToString("yyyy-MM-dd")); cell.CellStyle = ReportSubHeaderStyle; RowPos += 2; return(RowPos); }
protected ushort GenerateHierarchyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style, ushort groupRowCount) { if (groupRowCount <= 0) { return(rowPos); } //workSheet.Cells.Add(rowPos, colPos, currentHierarchyGroup); ushort colPos; Convert.ToUInt16(summaryStart - Convert.ToUInt16(1)); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos); NPOI.HSSF.UserModel.HSSFCell cell; for (colPos = 0; colPos < summaryStart - Convert.ToUInt16(1); colPos++) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellStyle = style; } cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue("Total:"); cell.CellStyle = style; colPos++; for (; colPos <= reportEndCol; colPos++) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos - groupRowCount, colPos) + ":" + ToCellString(rowPos - Convert.ToUInt16(1), colPos) + ")"; cell.CellStyle = style; } rowPos++; return(rowPos); }
private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime periodFromDate, DateTime periodToDate) { // create header styles NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); HeaderFont.Boldweight = 900; HeaderFont.FontHeightInPoints = 16; HeaderStyleLeft.SetFont(HeaderFont); NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft); HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft); HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Create header row NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5); HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleRight; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6); HeaderCell.SetCellValue("~"); HeaderCell.CellStyle = HeaderStyleCenter; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7); HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleLeft; }
//private ushort GenerateHeader(org.in2bits.MyXls.XlsDocument xlsDoc, org.in2bits.MyXls.Worksheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos) protected ushort GenerateHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle ReportHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ReportSubHeaderStyle) { //RowPos++; //workSheet.Cells.Add((int)RowPos, 1, "Payment Allocation Report"); //workSheet.Rows[RowPos].GetCell(1).Font.Bold = true; NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)workSheet.CreateRow((int)RowPos).CreateCell(0); cell.SetCellValue("Payment Allocation Report"); cell.CellStyle = ReportHeaderStyle; RowPos++; //workSheet.Cells.Add((int)RowPos, 1, "From"); //workSheet.Rows[RowPos].GetCell(1).Font.Bold = true; //workSheet.Cells.Add((int)RowPos, 2, PeriodFrom.ToString("yyyy-MM-dd")); //workSheet.Rows[RowPos].GetCell(2).Font.Bold = true; //workSheet.Cells.Add((int)RowPos, 3, "To"); //workSheet.Rows[RowPos].GetCell(3).Font.Bold = true; //workSheet.Cells.Add((int)RowPos, 4, PeriodTo.ToString("yyyy-MM-dd")); //workSheet.Rows[RowPos].GetCell(4).Font.Bold = true; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); cell.SetCellValue("From"); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1); cell.SetCellValue(PeriodFrom.ToString("yyyy-MM-dd")); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2); cell.SetCellValue("To"); cell.CellStyle = ReportSubHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3); cell.SetCellValue(PeriodTo.ToString("yyyy-MM-dd")); cell.CellStyle = ReportSubHeaderStyle; return(RowPos); }
public override FileInfo GenerateBankFile() { NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(System.IO.File.OpenRead(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ICBCBankFile.xls"))); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); int rowCount = 10; foreach (GenericBankFileDetail bankFileDetail in BankFileDetails) { NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowCount); if (row == null) { row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowCount); } NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(0); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); } cell.SetCellValue(bankFileDetail.EmpNo); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(1); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1); } cell.SetCellValue(bankFileDetail.EmpBankAccountHolderName); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(2); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2); } cell.SetCellValue(bankFileDetail.BankCode + bankFileDetail.BranchCode + bankFileDetail.AccountNo); cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(3); if (cell == null) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3); } cell.SetCellValue(bankFileDetail.Amount); rowCount++; } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workBook.Write(file); file.Close(); return(new FileInfo(exportFileName)); }
protected ushort GenerateCompanyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, DataTable paymentTable, int RecordCount, double companyEmployerTotal, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style) { //org.in2bits.MyXls.XF xf = xlsDoc.NewXF(); //xf.TopLineStyle = (ushort)1; rowPos++; ushort colPos = 0; //workSheet.Cells.Add(rowPos, colPos, "Total", xf); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue("Total"); cell.CellStyle = style; //**** Start 2013/11/22, Ricky So, adding Employee No. to excel output // Employee NO. has been added, and a empty cell is needed in grand total level if (intHierarchyLevelID == STAFF_LEVEL_ID) { colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellStyle = style; } //**** End 2013/11/22, Ricky So, adding Employee No. to excel output foreach (DataColumn column in paymentTable.Columns) { if (column.ColumnName.Equals("EmpPayrollID")) { continue; } colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round((double)companyTotalHashTable[column.ColumnName], 2), xf); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")"; cell.CellStyle = style; } colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round(companyNetPayment, 2), xf); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")"; cell.CellStyle = style; colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round(companyEmployerTotal, 2), xf); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")"; cell.CellStyle = style; return(rowPos); }
// public ushort GenerateHierarchyTotal(org.in2bits.MyXls.XlsDocument xlsDoc, org.in2bits.MyXls.Worksheet workSheet, DataTable paymentTable, string currentHierarchyGroup, Hashtable paymentHashTable, double netPayment, double employerContribution, ushort rowPos) protected ushort GenerateHierarchyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, DataTable paymentTable, string currentHierarchyGroup, string currentEmployeeNo, Hashtable paymentHashTable, double netPayment, double employerContribution, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style) { rowPos++; ushort colPos = 0; //workSheet.Cells.Add(rowPos, colPos, currentHierarchyGroup); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos); NPOI.HSSF.UserModel.HSSFCell cell; //**** Start 2013/11/22, Ricky So, adding Employee No. to excel output // write employee Number if (intHierarchyLevelID == STAFF_LEVEL_ID) { cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue(currentEmployeeNo); cell.CellStyle = style; colPos++; } //**** End 2013/11/22, Ricky So, adding Employee No. to excel output // write grouping value cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue(currentHierarchyGroup); cell.CellStyle = style; foreach (DataColumn column in paymentTable.Columns) { if (column.ColumnName.Equals("EmpPayrollID")) { continue; } colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round((double)paymentHashTable[column.ColumnName], 2)); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue(Math.Round((double)paymentHashTable[column.ColumnName], 2)); cell.CellStyle = style; } colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round(netPayment, 2)); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos, 1) + ":" + ToCellString(rowPos, colPos - 1) + ")"; cell.CellStyle = style; colPos++; //workSheet.Cells.Add(rowPos, colPos, Math.Round(employerContribution, 2)); //workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)"; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos); cell.SetCellValue(Math.Round(employerContribution, 2)); cell.CellStyle = style; return(rowPos); }
private void SetWorkSheetColumnWidth(NPOI.HSSF.UserModel.HSSFSheet workSheet, int countDay) { workSheet.SetColumnWidth(0, 4000); workSheet.SetColumnWidth(1, 4000); workSheet.SetColumnWidth(2, 9000); workSheet.SetColumnWidth(3, 4000); workSheet.SetColumnWidth(4, 2000); for (int dayIdx = 0; dayIdx < countDay; dayIdx++) { workSheet.SetColumnWidth(3 + dayIdx, 3000); } workSheet.SetColumnWidth(3 + countDay, 3000); }
private static DataTable ExcelInput(string FilePath) { //第一行一般为标题行。 DataTable table = new DataTable(); //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档,然后关闭文件 FileStream fsRead = File.OpenRead(FilePath); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead); fsRead.Close(); //获取excel的第一个sheet NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); //获取Excel的最大行数 int rowsCount = sheet.PhysicalNumberOfRows; //为保证Table布局与Excel一样,这里应该取所有行中的最大列数(需要遍历整个Sheet)。 //为少一交全Excel遍历,提高性能,我们可以人为把第0行的列数调整至所有行中的最大列数。 //int colsCount = sheet.GetRow(1).PhysicalNumberOfCells; //for (int i = 0; i < colsCount; i++) //{ // table.Columns.Add(i.ToString()); //} //for (int x = 0; x < rowsCount; x++) //{ // DataRow dr = table.NewRow(); // for (int y = 0; y < colsCount; y++) // { // dr[y] = sheet.GetRow(x).GetCell(y).ToString(); // } // table.Rows.Add(dr); //} //dataGridView标题 table.Columns.Add("原始值"); table.Columns.Add("修正值"); //取模板中第6列,从第二行开始循环取值 for (int x = 2; x < rowsCount; x++) { DataRow dr = table.NewRow(); dr[0] = sheet.GetRow(x).GetCell(6).ToString(); //第二列填入修改后的值 dr[1] = getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32); table.Rows.Add(dr); } sheet = null; workbook = null; return(table); }
protected ushort GenerateCompanyHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet, string CompanyHeader, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle) { NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); cell.SetCellValue("Company"); cell.CellStyle = GroupHeaderStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1); cell.SetCellValue(CompanyHeader); cell.CellStyle = GroupHeaderStyle; RowPos++; return(RowPos); }
private void buttonUpdateAndSaveFile_Click(object sender, EventArgs e) { if (!datasource.Equals("") && !excelsource.Equals("")) { //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档,然后关闭文件 FileStream fsRead = File.OpenRead(excelsource); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead); fsRead.Close(); //获取excel的第一个sheet NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); //获取Excel的最大行数 int rowsCount = sheet.PhysicalNumberOfRows; for (int x = 2; x < rowsCount; x++) { sheet.GetRow(x).GetCell(6).SetCellValue(getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32)); } //把编辑过后的工作薄重新保存为excel文件 SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "All files (*.*)|*.*|xls files (*.xls)|*.xls"; dialog.FilterIndex = 2; dialog.RestoreDirectory = true; dialog.ShowDialog(); if (!string.IsNullOrEmpty(dialog.FileName)) { FileStream fsWrite = File.Create(@dialog.FileName); workbook.Write(fsWrite); fsWrite.Close(); } } else { MessageBox.Show("都不加载下数据就搞?重头来过!"); datasource = ""; excelsource = ""; textBoxExcelUrl.Text = ""; textBoxDbUrl.Text = ""; } }
/// <summary> /// 读取excel2003的图片 /// </summary> /// <param name="sheet">当前sheet对象</param> /// <returns></returns> private Dictionary <string, Byte[]> getSheetPictrues03(NPOI.HSSF.UserModel.HSSFSheet sheet) { Dictionary <string, Byte[]> sheetIndexPicMap = new Dictionary <string, Byte[]>(); System.Collections.IList pictures = sheet.Workbook.GetAllPictures(); if (pictures.Count > 0) { NPOI.HSSF.UserModel.HSSFPatriarch hp = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.DrawingPatriarch; foreach (NPOI.HSSF.UserModel.HSSFShape hs in hp.Children) { NPOI.HSSF.UserModel.HSSFClientAnchor anchor = (NPOI.HSSF.UserModel.HSSFClientAnchor)hs.Anchor; NPOI.HSSF.UserModel.HSSFPicture pic = (NPOI.HSSF.UserModel.HSSFPicture)hs; NPOI.HSSF.UserModel.HSSFPictureData picData = (NPOI.HSSF.UserModel.HSSFPictureData)pic.PictureData; string pciname = anchor.Row1.ToString() + "." + picData.SuggestFileExtension(); sheetIndexPicMap.Add(pciname, picData.Data); } } return(sheetIndexPicMap); }
private void AddSubTotalRow(NPOI.HSSF.UserModel.HSSFSheet excelWorksheet, string currentCompany, int recordCount, int lastRowIndex, int lastColumnIndex, NPOI.HSSF.UserModel.HSSFRow headerRow, NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle) { //execlWorksheet.Cells[lastRowIndex, PAYMENTDETAIL_START_COLUMN - 1].Value = "Subtotal - " + currentCompany; NPOI.HSSF.UserModel.HSSFRow subTotalRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex); NPOI.HSSF.UserModel.HSSFCell subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(0); subTotalCell.SetCellValue("Subtotal - " + currentCompany); subTotalCell.CellStyle = subTotalStyle; excelWorksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastRowIndex, lastRowIndex, 0, PAYMENTDETAIL_START_COLUMN - 1)); for (int iCol = PAYMENTDETAIL_START_COLUMN; iCol <= lastColumnIndex; iCol++) { //double totalPayment = 0; //bool IsTotalPaymentNull = true; //for (int iRow = lastRowIndex - recordCount; iRow < lastRowIndex; iRow++) //{ // ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[iRow, iCol]; // if (cell.Value is double) // { // IsTotalPaymentNull = false; // totalPayment += (double)cell.Value; // } //} //if (!IsTotalPaymentNull) //{ // ExcelLibrary.SpreadSheet.Cell totalCell = execlWorksheet.Cells[lastRowIndex, iCol]; // totalCell.FormatString = "#,##0.00"; // totalCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(totalPayment, 2, 2); //} if (headerRow.GetCell(iCol) != null) { subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(iCol); subTotalCell.CellFormula = "SUM(" + ToCellString(lastRowIndex - recordCount, iCol) + ":" + ToCellString(lastRowIndex - 1, iCol) + ")"; subTotalCell.CellStyle = subTotalStyle; } } }
//private ExcelLibrary.SpreadSheet.Worksheet CreateWorkSheet(DataTable dataTable) private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(DataTable dataTable) { if (workbook != null) { NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericCellStyle.DataFormat = format.GetFormat("0.00##"); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", "")); //ExcelLibrary.SpreadSheet.Row headerRow = new ExcelLibrary.SpreadSheet.Row(); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); int columnCount = 0; foreach (DataColumn headercolumn in dataTable.Columns) { headercolumn.ColumnName = headercolumn.ColumnName.Trim(); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); cell.SetCellValue(headercolumn.ColumnName); //headerRow.SetCell(columnCount,cell); //worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); columnCount++; } //worksheet.Cells.Rows.Add(0, headerRow); int rowCount = 0; //NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ; //NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ; foreach (DataRow row in dataTable.Rows) { rowCount++; columnCount = 0; // ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row(); NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); foreach (DataColumn column in dataTable.Columns) { //ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount); if (column.DataType.Equals(typeof(string))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); } else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00"); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]); if (row[column] != System.DBNull.Value) { double value = Convert.ToDouble(row[column].ToString()); if (value.Equals(double.NaN)) { cell.SetCellValue(string.Empty); } else { cell.SetCellValue(value); } } cell.CellStyle = numericCellStyle; } else if (column.DataType.Equals(typeof(int))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00"); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]); if (row[column] != System.DBNull.Value) { cell.SetCellValue(Convert.ToDouble(row[column].ToString())); } //cell.CellStyle = integerCellStyle; } else if (column.DataType.Equals(typeof(DateTime))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd"); //if (row[column] == System.DBNull.Value) // cell.Value = string.Empty; //else // cell.Value = (DateTime)row[column]; if (row[column] != System.DBNull.Value) { cell.SetCellValue((DateTime)row[column]); } cell.CellStyle = dateCellStyle; } else { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); if (row[column] != System.DBNull.Value) { cell.SetCellValue(row[column].ToString()); } } //worksheet.Cells[rowCount, columnCount] = cell; columnCount++; } // worksheet.Cells.Rows.Add(rowCount, detailRow); } //workbook.Worksheets.Add(worksheet); return(worksheet); } else { return(null); } }
protected void btnGenerate_Click(object sender, EventArgs e) { const string FIELD_COMPANY = "Company"; const string FIELD_POSITION = "Position"; const string FIELD_PAYROLLGROUP = "Payroll Group"; const string FIELD_EMPNO = "EmployeeID"; const string FIELD_EMPENGFULLNAME = "English Name"; const string FIELD_CHINESENAME = "¤¤¤å©m¦W"; const string FIELD_HKID = @"HKID/Passport"; const string FIELD_PERIODFROM = "From"; const string FIELD_PERIODTO = "To"; const string FIELD_WAGESWORK = "Wages Paid"; const string FIELD_WORKHOURTOTAL = "Total Working Hours"; const string FIELD_RESTDAYTOTAL = "No. of Rest Day"; const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH"; const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave"; const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave"; ArrayList list = new ArrayList(); foreach (RepeaterItem i in Repeater.Items) { CheckBox cb = (CheckBox)i.FindControl("ItemSelect"); if (cb.Checked) { EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject(); WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb); list.Add(o); } } ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList(); if (list.Count > 0 && payPeriodList.Count > 0) { //const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] "; string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; //System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true); HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName); DataSet dataSet = new DataSet(); //export.GetDataSet(); DataTable dataTable = new DataTable("Payroll$"); dataSet.Tables.Add(dataTable); dataTable.Columns.Add(FIELD_COMPANY, typeof(string)); DBFilter hierarchyLevelFilter = new DBFilter(); Hashtable hierarchyLevelHashTable = new Hashtable(); hierarchyLevelFilter.add("HLevelSeqNo", true); ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter); foreach (EHierarchyLevel hlevel in hierarchyLevelList) { dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string)); hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } dataTable.Columns.Add(FIELD_POSITION, typeof(string)); dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string)); dataTable.Columns.Add(FIELD_EMPNO, typeof(string)); dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string)); dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string)); dataTable.Columns.Add(FIELD_HKID, typeof(string)); dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime)); dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime)); dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double)); dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double)); dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double)); int firstSummaryColumnPos = dataTable.Columns.Count; int firstDetailColumnPos = dataTable.Columns.Count; foreach (EPayrollPeriod payPeriod in payPeriodList) { if (EPayrollPeriod.db.select(dbConn, payPeriod)) { EPayrollGroup payrollGroup = new EPayrollGroup(); payrollGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payrollGroup); foreach (EEmpPersonalInfo empInfo in list) { EEmpPersonalInfo.db.select(dbConn, empInfo); EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID)); empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID)); if (PayrollStatus.SelectedValue.Equals("T")) { empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T")); } else { empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T")); } DataRow row = dataTable.NewRow(); row[FIELD_EMPNO] = empInfo.EmpNo; row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName; row[FIELD_CHINESENAME] = empInfo.EmpChiFullName; row[FIELD_HKID] = empInfo.EmpHKID; row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr; row[FIELD_PERIODTO] = payPeriod.PayPeriodTo; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID); if (empPos != null) { ECompany company = new ECompany(); company.CompanyID = empPos.CompanyID; if (ECompany.db.select(dbConn, company)) { row[FIELD_COMPANY] = company.CompanyCode; } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row[FIELD_POSITION] = position.PositionDesc; } DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID]; if (hierarchyLevel != null) { EHierarchyElement hierarchyElement = new EHierarchyElement(); hierarchyElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hierarchyElement)) { row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc; } } } EPayrollGroup curentPayGroup = new EPayrollGroup(); curentPayGroup.PayGroupID = empPos.PayGroupID; if (EPayrollGroup.db.select(dbConn, curentPayGroup)) { row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc; } } double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0; double mcER = 0, mcEE = 0; double vcER = 0, vcEE = 0; double pFundER = 0, pFundEE = 0; double wagesByWork = 0; double wagesByRest = 0; double fullPaidLeaveDays = 0; double nonFullPaidLeaveDays = 0; DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); paymentRecordFilter.add(new Match("PayRecStatus", "A")); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); foreach (EPaymentRecord paymentRecord in paymentRecords) { EPaymentCode payCode = new EPaymentCode(); payCode.PaymentCodeID = paymentRecord.PaymentCodeID; EPaymentCode.db.select(dbConn, payCode); // Always Use Payment Code Description for grouping payment code with same description //string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc; //if (dataTable.Columns[fieldName] == null) // dataTable.Columns.Add(new DataColumn(fieldName, typeof(double))); //if (row[fieldName] == null || row[fieldName] == DBNull.Value) // row[fieldName] = 0; //row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount; netAmount += paymentRecord.PayRecActAmount; if (payCode.PaymentCodeIsMPF) { releventIncome += paymentRecord.PayRecActAmount; } else { nonRelevantIncome += paymentRecord.PayRecActAmount; } DBFilter taxPaymentMapFilter = new DBFilter(); taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID)); if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0) { taxableAmount += paymentRecord.PayRecActAmount; } else { nonTaxableAmount += paymentRecord.PayRecActAmount; } if (payCode.PaymentCodeIsWages) { if (paymentRecord.PayRecIsRestDayPayment) { wagesByRest += paymentRecord.PayRecActAmount; } else { wagesByWork += paymentRecord.PayRecActAmount; } } } DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); foreach (EMPFRecord mpfRecord in mpfRecords) { vcER += mpfRecord.MPFRecActVCER; mcER += +mpfRecord.MPFRecActMCER; vcEE += mpfRecord.MPFRecActVCEE; mcEE += mpfRecord.MPFRecActMCEE; } ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter); foreach (EORSORecord orsoRecord in orsoRecords) { pFundER += orsoRecord.ORSORecActER; pFundEE += orsoRecord.ORSORecActEE; } row[FIELD_WAGESWORK] = wagesByWork; DBFilter workingSummaryFilter = new DBFilter(); workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo)); } workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID)); ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter); double workHourTotal = 0, restDayTotal = 0; foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList) { workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours; restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled; } row[FIELD_WORKHOURTOTAL] = workHourTotal; row[FIELD_RESTDAYTOTAL] = restDayTotal; DBFilter statutoryHolidayFilter = new DBFilter(); statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo)); } ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter); double restDayCount = 0; foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList) { restDayCount++; } row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount; DBFilter LeaveAppEmpPayrollFilter = new DBFilter(); LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter); foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { if (leaveCode.LeaveCodePayRatio >= 1) { fullPaidLeaveDays += leaveApp.LeaveAppDays; } else { nonFullPaidLeaveDays += leaveApp.LeaveAppDays; } } } row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays; row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays; dataTable.Rows.Add(row); } } } //DBFilter paymentCodeFilter = new DBFilter(); //paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false); //paymentCodeFilter.add("PaymentCode", false); //ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter); //foreach (EPaymentCode paymentCode in paymentCodeList) //{ // if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc)) // { // DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc]; // paymentColumn.SetOrdinal(firstDetailColumnPos); // if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc)) // paymentColumn.ColumnName = paymentCode.PaymentCodeDesc; // else // { // Console.Write("System reserved payment column is used"); // } // } //} //for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++) // dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1); export.Update(dataSet); System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1); NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0); if (excelRow == null) { excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); } NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0); if (excelCell == null) { excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0); } excelCell.SetCellValue("Statutory Minimum Wage Summary Report"); excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); workbook.Write(excelfileStream); excelfileStream.Close(); WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } }
protected System.IO.FileInfo GenerateExcelFile() { DateTime periodFromDate; DateTime periodToDate; if (DateTime.TryParse(PeriodFrDate.Value, out periodFromDate) && DateTime.TryParse(PeriodToDate.Value, out periodToDate)) { int countDay = periodToDate.AddDays(1).Subtract(periodFromDate).Days;// get days from periodFromDate to periodToDate List <EHierarchyLevel> hLevelList = GetHierarchyLevelList(dbConn); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(); SetWorkSheetColumnWidth(workSheet, countDay); createWorkSheetPeriodRow(workbook, workSheet, periodFromDate, periodToDate); createWorkSheetHeaderRows(workbook, workSheet, hLevelList, countDay, periodFromDate, periodToDate); // load data detail start ArrayList list = LoadRosterTableGroup(periodFromDate, periodToDate, CurID); if (list.Count >= 1) { selectedRosterTableGroupID = ((ERosterTableGroup)list[0]).RosterTableGroupID; BuildCommonRosterCodeAndTermsFilter(); Dictionary <int, List <DateTime> > staffRosterDateList = LoadEmployeeList(periodFromDate, periodToDate); int RowPos = 0; foreach (int empID in staffRosterDateList.Keys) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = empID; if (!EEmpPersonalInfo.db.select(dbConn, empInfo)) { continue; } string positionTitle = string.Empty; List <string> hElementDescList = new List <string>(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, periodFromDate, empID); if (empPos != null) { EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { positionTitle = position.PositionDesc; } foreach (EHierarchyLevel hLevel in hLevelList) { string hElementDesc = string.Empty; DBFilter empHierarchy1Filter = new DBFilter(); empHierarchy1Filter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchy1Filter.add(new Match("HLevelID", hLevel.HLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchy1Filter); if (empHierarchyList.Count > 0) { EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = ((EEmpHierarchy)empHierarchyList[0]).HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { hElementDesc = hElement.HElementDesc; } } hElementDescList.Add(hElementDesc); } } createWorkSheetDetailRows(workbook, workSheet, RowPos, hElementDescList, empInfo, positionTitle, countDay, periodFromDate, empID); RowPos += 2; } } // load data detail end string strTmpFolder = HROne.Common.Folder.GetOrCreateSessionTempFolder(Session.SessionID).FullName; string strTmpFile = System.IO.Path.Combine(strTmpFolder, AppUtils.ServerDateTime().ToString("~yyyyMMddHHmmss_AttendanceTimeCardRecordReport.xls")); System.IO.Stream fileoutputstream = new System.IO.FileStream(strTmpFile, System.IO.FileMode.OpenOrCreate); workbook.Write(fileoutputstream); fileoutputstream.Close(); return(new System.IO.FileInfo(strTmpFile)); } return(null); }
private void createWorkSheetHeaderRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, List <EHierarchyLevel> hLevelList, int countDay, DateTime periodFromDate, DateTime periodToDate) { // Create column header styles NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; columnHeaderStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFFont columnHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); columnHeaderFont.Boldweight = 900; columnHeaderStyle.SetFont(columnHeaderFont); columnHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleFirstTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleFirstTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleFirstTop.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleMiddleTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleMiddleTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleLastTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleLastTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleLastTop.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleFirstBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleFirstBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleFirstBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleMiddleBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleMiddleBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleLastBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleLastBottom.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleLastBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; // Create column header rows const int COLUMN_HEADER_ROW = 2; NPOI.HSSF.UserModel.HSSFRow columnHeaderRow1 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + 1); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell; int colpos = 0; foreach (EHierarchyLevel hLevel in hLevelList) { columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue(hLevel.HLevelDesc); columnHeaderCell.CellStyle = columnHeaderStyleFirstTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleFirstBottom; colpos++; } columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Name"); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Title"); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Staff No."); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; for (int colIdx = 0; colIdx < colpos; colIdx++) { workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colIdx, colIdx)); } for (int dayIdx = 0; dayIdx < countDay; dayIdx++) { DateTime currentDate = periodFromDate.AddDays(dayIdx); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos + dayIdx); columnHeaderCell.SetCellValue(currentDate.ToString("dd-MMM-yy")); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos + dayIdx); columnHeaderCell.SetCellValue(currentDate.ToString("ddd")); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; } colpos += countDay; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Signature"); columnHeaderCell.CellStyle = columnHeaderStyleLastTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleLastBottom; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colpos, colpos)); }
private void GenerateExcelReport(DataSet dataSet, string exportFileName) { //ExcelLibrary.SpreadSheet.Worksheet execlWorksheet = null; DataView tmpView = new DataView(dataSet.Tables["Hierarchy"]); tmpView.Sort = "CompanyID, PayGroupID, HElementID Desc,CostCenterID"; DataTable sortedHierarchyTable = tmpView.ToTable(); int curringCompanyID = 0; string currentCompany = string.Empty; int recordCount = 0; int lastRowIndex = 0, lastColumnIndex = 0; //ExcelLibrary.SpreadSheet.Workbook excelWorkbook = new ExcelLibrary.SpreadSheet.Workbook(); //execlWorksheet = new ExcelLibrary.SpreadSheet.Worksheet("CostAllocation"); //excelWorkbook.Worksheets.Add(execlWorksheet); NPOI.HSSF.UserModel.HSSFWorkbook excelWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet excelWorksheet = (NPOI.HSSF.UserModel.HSSFSheet)excelWorkbook.CreateSheet("CostAllocation"); NPOI.HSSF.UserModel.HSSFRow headerRow = null; NPOI.HSSF.UserModel.HSSFFont headerFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont(); headerFont.Boldweight = 1; NPOI.HSSF.UserModel.HSSFCellStyle headerStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle(); headerStyle.SetFont(headerFont); //headerStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THICK; NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle(); numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00"); NPOI.HSSF.UserModel.HSSFFont subTotalFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont(); subTotalFont.Boldweight = 1; NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle(); subTotalStyle.SetFont(subTotalFont); //subTotalStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THICK; subTotalStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00"); foreach (DataRow row in sortedHierarchyTable.Rows) { if (curringCompanyID != (int)row["CompanyID"]) { if (recordCount > 0) { lastRowIndex++; AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle); recordCount = 0; lastRowIndex++; } //else { //execlWorksheet.Cells[lastRowIndex, 0].Value = row["Company"].ToString(); //lastRowIndex += FIELD_HEADER_ROW; //execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = "Payroll Group"; //execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = "Cost Ctr"; //execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = "Hierarchy"; NPOI.HSSF.UserModel.HSSFRow companyRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex); companyRow.CreateCell(0).SetCellValue(row["Company"].ToString()); lastRowIndex += FIELD_HEADER_ROW; headerRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex); headerRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue("Payroll Group"); headerRow.CreateCell(COST_CENTER_COLUMN).SetCellValue("Cost Ctr"); headerRow.CreateCell(HIERARCHY_COLUMN).SetCellValue("Hierarchy"); } curringCompanyID = (int)row["CompanyID"]; currentCompany = row["Company"].ToString(); } lastRowIndex++; recordCount++; //lastRowIndex = FIELD_HEADER_ROW + recordCount; //execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = row["PayrollGroupDesc"]; //execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = row["CostCenterDesc"]; //execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = row["HierarchyDesc"]; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex); detailRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue(row["PayrollGroupDesc"].ToString()); detailRow.CreateCell(COST_CENTER_COLUMN).SetCellValue(row["CostCenterDesc"].ToString()); detailRow.CreateCell(HIERARCHY_COLUMN).SetCellValue(row["HierarchyDesc"].ToString()); lastColumnIndex = PAYMENTDETAIL_START_COLUMN - 1; DataRow[] paymentRows = dataSet.Tables["Payment"].Select("ID=" + row["ID"]); if (paymentRows.Length > 0) { //double netPayment = 0; DataRow paymentRow = paymentRows[0]; foreach (DataColumn column in paymentRow.Table.Columns) { if (!column.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase)) { lastColumnIndex++; if (recordCount == 1) { //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = column.ColumnName; headerRow.CreateCell(lastColumnIndex).SetCellValue(column.ColumnName); } if (!paymentRow.IsNull(column)) { //ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex]; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2); //cell.FormatString = "#,##0.00"; //netPayment += HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2); NPOI.HSSF.UserModel.HSSFCell dataCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex); dataCell.CellStyle = numericStyle; dataCell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2)); } } } lastColumnIndex++; if (recordCount == 1) { //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = "NET PAYMENT"; headerRow.CreateCell(lastColumnIndex).SetCellValue("NET PAYMENT"); } //ExcelLibrary.SpreadSheet.Cell netPaymentCell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex]; //netPaymentCell.FormatString = "#,##0.00"; //netPaymentCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netPayment, 2, 2); NPOI.HSSF.UserModel.HSSFCell netPaymentCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex); netPaymentCell.CellStyle = numericStyle; netPaymentCell.CellFormula = "SUM(" + ToCellString(lastRowIndex, PAYMENTDETAIL_START_COLUMN) + ":" + ToCellString(lastRowIndex, lastColumnIndex - 1) + ")"; } lastColumnIndex++; DataRow[] contributionRows = dataSet.Tables["contribution"].Select("ID=" + row["ID"]); if (contributionRows.Length > 0) { DataRow contributionRow = contributionRows[0]; if (recordCount == 1) { //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 1].Value = "MPF (ER)"; //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 2].Value = "VC (ER)"; //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 3].Value = "P-FUND (ER)"; //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 4].Value = "Total Contribution (ER)"; //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 6].Value = "Total Contribution (EE)"; headerRow.CreateCell(lastColumnIndex + 1).SetCellValue("MPF (ER)"); headerRow.CreateCell(lastColumnIndex + 2).SetCellValue("VC (ER)"); headerRow.CreateCell(lastColumnIndex + 3).SetCellValue("P-FUND (ER)"); headerRow.CreateCell(lastColumnIndex + 4).SetCellValue("Total Contribution (ER)"); headerRow.CreateCell(lastColumnIndex + 6).SetCellValue("Total Contribution (EE)"); } //ExcelLibrary.SpreadSheet.Cell cell=execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 1]; //cell.FormatString = "#,##0.00"; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2); //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 2]; //cell.FormatString = "#,##0.00"; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2); //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 3]; //cell.FormatString = "#,##0.00"; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2); //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 4]; //cell.FormatString = "#,##0.00"; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2); //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 6]; //cell.FormatString = "#,##0.00"; //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 1); cell.CellStyle = numericStyle; cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2)); cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 2); cell.CellStyle = numericStyle; cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2)); cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 3); cell.CellStyle = numericStyle; cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2)); cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 4); cell.CellStyle = numericStyle; cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2)); cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 6); cell.CellStyle = numericStyle; cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2)); } lastColumnIndex += 6; } lastRowIndex++; AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle); //excelWorkbook.Save(exportFileName); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); excelWorkbook.Write(file); file.Close(); }
private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy) { DataTable dataTable = new DataTable(); dataTable.Columns.Add("EmpID", typeof(int)); dataTable.Columns.Add("EmpNo", typeof(string)); dataTable.Columns.Add("EmpName", typeof(string)); dataTable.Columns.Add("EmpAlias", typeof(string)); dataTable.Columns.Add("Company", typeof(string)); DBFilter hierarchyLevelFilter = new DBFilter(); Hashtable hierarchyLevelHashTable = new Hashtable(); hierarchyLevelFilter.add("HLevelSeqNo", true); ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter); foreach (EHierarchyLevel hlevel in hierarchyLevelList) { dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string)); hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } dataTable.Columns.Add("Position", typeof(string)); dataTable.Columns.Add("TrainingSeminarID", typeof(int)); dataTable.Columns.Add("TrainingCourseName", typeof(string)); dataTable.Columns.Add("TrainingSeminarDesc", typeof(string)); dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime)); dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime)); dataTable.Columns.Add("TrainingSeminarDuration", typeof(double)); dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string)); dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string)); foreach (EEmpPersonalInfo empInfo in empList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID); ECompany company = new ECompany(); EPosition position = new EPosition(); ArrayList empHierarchyList = new ArrayList(); if (empPos != null) { company.CompanyID = empPos.CompanyID; ECompany.db.select(dbConn, company); //row["Company"] = company.CompanyCode; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); //foreach (EEmpHierarchy empHierarchy in empHierarchyList) //{ // EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID]; // if (hierarchyLevel != null) // { // EHierarchyElement hierarchyElement = new EHierarchyElement(); // hierarchyElement.HElementID = empHierarchy.HElementID; // if (EHierarchyElement.db.select(dbConn, hierarchyElement)) // row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc; // } //} position.PositionID = empPos.PositionID; EPosition.db.select(dbConn, position); } DBFilter empTrainingSeminar = new DBFilter(); empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID)); OR orTrainingCourse = null; foreach (ListItem item in TrainingCourseList.Items) { if (item.Selected) { if (orTrainingCourse == null) { orTrainingCourse = new OR(); } orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value)); } } DBFilter trainingSeminarFilter = new DBFilter(); if (!PeriodFrom.Ticks.Equals(0)) { trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom)); } if (!PeriodTo.Ticks.Equals(0)) { trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo)); } if (orTrainingCourse != null) { trainingSeminarFilter.add(orTrainingCourse); } empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter)); ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar); foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList) { ETrainingSeminar trainingSeminar = new ETrainingSeminar(); trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID; if (ETrainingSeminar.db.select(dbConn, trainingSeminar)) { DataRow row = dataTable.NewRow(); row["EmpID"] = empInfo.EmpID; row["EmpNo"] = empInfo.EmpNo; row["EmpName"] = empInfo.EmpEngFullName; row["EmpAlias"] = empInfo.EmpAlias; row["Company"] = company.CompanyCode; foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID]; if (hierarchyLevel != null) { EHierarchyElement hierarchyElement = new EHierarchyElement(); hierarchyElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hierarchyElement)) { row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc; } } } row["Position"] = position.PositionDesc; row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID; ETrainingCourse trainingCourse = new ETrainingCourse(); trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID; if (ETrainingCourse.db.select(dbConn, trainingCourse)) { row["TrainingCourseName"] = trainingCourse.TrainingCourseName; } else { row["TrainingCourseName"] = string.Empty; } row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc; row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom; row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo; row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration; if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H")) { row["TrainingSeminarDurationUnit"] = "Hour(s)"; } else { row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit; } row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer; dataTable.Rows.Add(row); } } } } //org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument(); //org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report"); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report"); NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); reportHeaderStyle.SetFont(boldFont); NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleCenter.SetFont(boldFont); columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00"); int rowCount = 0; //worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true; NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0); reportHeaderCell.SetCellValue("Training Report"); reportHeaderCell.CellStyle = reportHeaderStyle; rowCount++; if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy")); rowCount++; } else if (!PeriodTo.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy")); rowCount++; } else if (!PeriodFrom.Ticks.Equals(0)) { rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy")); worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy")); rowCount++; } if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo"; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true; //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty); //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true; //worksheet.Cells.Merge(rowCount, rowCount, 4, 5); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } int currentTrainingSeminarID = 0; foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"])) { if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } } currentTrainingSeminarID = ((int)row["TrainingSeminarID"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]); //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]); //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]); detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString()); detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(3).CellStyle = numericStyle; detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString()); detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(9).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(10).SetCellValue(row["Company"].ToString()); colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } } else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName "; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true; NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 4; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty); //worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5); //worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; int currentEmpID = 0; foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); if (!currentEmpID.Equals((int)row["EmpID"])) { //worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]); detailRow.CreateCell(0).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(4).SetCellValue(row["Company"].ToString()); colCount = 4; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } currentEmpID = ((int)row["EmpID"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]); detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString()); if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]); detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(colCount + 4).CellStyle = numericStyle; detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString()); } } else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase)) { DataView dataView = new DataView(dataTable); dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo"; DataTable sortedTable = dataView.ToTable(); rowCount++; //worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true; //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered; //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty); //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true; //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true; //worksheet.Cells.Merge(rowCount, rowCount, 4, 5); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0); columnHeaderCell.SetCellValue("Course Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1); columnHeaderCell.SetCellValue("Description"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2); columnHeaderCell.SetCellValue("Training Date"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3); columnHeaderCell.SetCellValue("Duration"); columnHeaderCell.CellStyle = columnHeaderStyleCenter; worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4)); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5); columnHeaderCell.SetCellValue("Trainer"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6); columnHeaderCell.SetCellValue("Employee No."); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7); columnHeaderCell.SetCellValue("Employee Name"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8); columnHeaderCell.SetCellValue("Alias"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9); columnHeaderCell.SetCellValue("Position"); columnHeaderCell.CellStyle = reportHeaderStyle; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10); columnHeaderCell.SetCellValue("Company"); columnHeaderCell.CellStyle = reportHeaderStyle; int colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount); columnHeaderCell.SetCellValue(hlevel.HLevelDesc); columnHeaderCell.CellStyle = reportHeaderStyle; } foreach (DataRow row in sortedTable.Rows) { rowCount++; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); //worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]); //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]); detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString()); detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString()); if (row["TrainingSeminarDateFrom"] != DBNull.Value) { DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]); DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]); if (trainingFrom.Equals(trainingTo)) { //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy")); detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy")); } else { //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy")); } } //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]); //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]); //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]); //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]); //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]); //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]); //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]); //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]); detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]); //detailRow.GetCell(3).CellStyle = numericStyle; detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString()); detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString()); detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString()); detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString()); detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString()); detailRow.CreateCell(9).SetCellValue(row["Position"].ToString()); detailRow.CreateCell(10).SetCellValue(row["Company"].ToString()); colCount = 10; foreach (EHierarchyLevel hlevel in hierarchyLevelList) { colCount++; if (row[hlevel.HLevelDesc] != DBNull.Value) { //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]); detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString()); } } } } string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; //document.FileName = exportFileName; //document.Save(); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls"; WebUtils.TransmitFile(Response, exportFileName, filename, true); return; }
public static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName) { //OleDbConnection cnnxls = null; DataSet ds = new DataSet(); if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase)) { string strTmpFolder = ExcelFilePath + ".dir"; try { zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword); System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder); foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories)) { ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName)); } } catch (Exception ex) { throw ex; } finally { System.IO.Directory.Delete(strTmpFolder, true); } //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\""; //cnnxls = new OleDbConnection(strConn); //cnnxls.Open(); } else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase)) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath); DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\""); table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName); ds.Tables.Add(table); } else { NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++) { if (!workBook.IsSheetHidden(sheetIndex)) { int intHeaderRow = 0; NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex); NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); if (!string.IsNullOrEmpty(FirstColumnName)) { for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++) { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx); if (headerRow == null) { continue; } bool columnNameMatch = false; for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++) { if (headerRow.GetCell(tmpColumnIndex) != null) { string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim(); if (FirstColumnName.Equals(columnName)) { intHeaderRow = tmpRowIdx; columnNameMatch = true; break; } } } if (columnNameMatch) { break; } } } else { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); } if (headerRow == null) { continue; } string tableName = workSheet.SheetName.Trim(); DataTable table = new DataTable(tableName); int intColumnIndex = 0; while (intColumnIndex <= headerRow.LastCellNum) { if (headerRow.GetCell(intColumnIndex) != null) { string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim(); if (string.IsNullOrEmpty(columnName)) { columnName = "Column_" + intColumnIndex; } if (table.Columns.Contains(columnName)) { columnName = "Column_" + intColumnIndex; } table.Columns.Add(columnName, typeof(string)); // resign new value of column name to Excel for below part of import headerRow.GetCell(intColumnIndex).SetCellValue(columnName); } intColumnIndex++; } int rowCount = 1; while (intHeaderRow + rowCount <= workSheet.LastRowNum) { int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount); if (row == null) { rowCount++; continue; } DataRow dataRow = table.NewRow(); while (colCount <= headerRow.LastCellNum) { if (headerRow.GetCell(colCount) != null) { string columnName = headerRow.GetCell(colCount).ToString(); if (table.Columns.Contains(columnName)) { NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount); if (cell != null) { if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA)) { NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook); cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell); } string fieldValue = cell.ToString(); if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { string format = string.Empty; //bool IsBuildinformat = false; // Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format try { format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat); //IsBuildinformat = true; } catch { format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat); } // [h]:mm:ss handle NOT support int midBlanketStartPos = format.IndexOf('['); while (midBlanketStartPos >= 0) { int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos); format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1); midBlanketStartPos = format.IndexOf('['); } if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0) { if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { DateTime date = cell.DateCellValue; if (date.TimeOfDay.TotalSeconds > 0) { fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = date.ToString("yyyy-MM-dd"); } } } else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { DateTime date = cell.DateCellValue; // default date of "Time Only" field is 1899-12-31 if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks)) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = cell.DateCellValue.ToString("HH:mm:ss"); } } else { fieldValue = cell.NumericCellValue.ToString(); } } dataRow[columnName] = fieldValue; } } } colCount++; } table.Rows.Add(dataRow); rowCount++; } ds.Tables.Add(table); } } //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\""; //cnnxls = new OleDbConnection(strConn); //try //{ // cnnxls.Open(); //} //catch //{ // cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\""; // cnnxls.Open(); //} //DataTable schemaTable = cnnxls.GetSchema("Tables"); //foreach (DataRow schemaRow in schemaTable.Rows) //{ // string tableName = schemaRow["Table_Name"].ToString().Trim(); // if (tableName.EndsWith("$")) // { // OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls); // try // { // //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);// // //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", ""); // //if (tables[0].Columns.Contains("Emp No*")) // // tables[0].Columns["Emp No*"].DataType = typeof(string); // //OleDbDataReader dr = oda.SelectCommand.ExecuteReader(); // //while (dr.Read()) // //{ // // DataRow row = tables[0].NewRow(); // // for (int i = 0; i < tables[0].Columns.Count; i++) // // row[i] = dr[i]; // // tables[0].Rows.Add(row); // //} // //// oda.Fill(tables[0]); // //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$")) // // ds.Tables.Remove(tableName); // string actualTableName = tableName.Substring(0, tableName.Length - 1); // if (!ds.Tables.Contains(actualTableName)) // oda.Fill(ds, actualTableName); // } // catch // { // // unknown error caused by hidden sheet // } // // oda.Fill(ds); // } //} //cnnxls.Close(); } foreach (DataTable tempTable in ds.Tables) { for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--) { DataRow row = tempTable.Rows[rowIdx]; bool isEmptyRow = true; foreach (DataColumn tempColumn in tempTable.Columns) { if (!row.IsNull(tempColumn)) { if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim())) { isEmptyRow = false; break; } } } if (isEmptyRow) { tempTable.Rows.Remove(row); } else { break; } } } foreach (DataTable tempTable in ds.Tables) { foreach (DataColumn tempColumn in tempTable.Columns) { string tempColumnName = tempColumn.ColumnName; tempColumnName = tempColumnName.Trim().Replace("*", ""); tempColumnName = tempColumnName.Trim().Replace("#", ""); tempColumn.ColumnName = tempColumnName; } } return(ds); }
private void createWorkSheetDetailRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, int RowPos, List <string> hElementDescList, EEmpPersonalInfo empInfo, string positionTitle, int countDay, DateTime periodFromDate, int empID) { // Create column detail styles NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; detailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleCenterAlignment = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleCenterAlignment.CloneStyleFrom(detailStyle); detailStyleCenterAlignment.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleFirst = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleFirst.CloneStyleFrom(detailStyle); detailStyleFirst.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleLast = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleLast.CloneStyleFrom(detailStyle); detailStyleLast.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; // Create column detail rows const int DETAIL_START_ROW = 4; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos); NPOI.HSSF.UserModel.HSSFRow detailRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos + 1); // second row of record NPOI.HSSF.UserModel.HSSFCell detailCell; NPOI.HSSF.UserModel.HSSFCell detailCell2; int colpos = 0; foreach (string hElementDesc in hElementDescList) { workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(hElementDesc); detailCell.CellStyle = detailStyleFirst; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(hElementDesc); detailCell2.CellStyle = detailStyleFirst; colpos++; } workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(empInfo.EmpEngFullName); detailCell.CellStyle = detailStyleFirst; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(empInfo.EmpEngFullName); detailCell2.CellStyle = detailStyleFirst; colpos++; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(positionTitle); detailCell.CellStyle = detailStyle; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(positionTitle); detailCell2.CellStyle = detailStyle; colpos++; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(empInfo.EmpNo); detailCell.CellStyle = detailStyle; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(empInfo.EmpNo); detailCell2.CellStyle = detailStyle; colpos++; for (int dayIdx = 0; dayIdx < countDay; dayIdx++) { //first line of employee record detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos + dayIdx); detailCell.CellStyle = detailStyleCenterAlignment; // second line of employee data detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos + dayIdx); detailCell2.CellStyle = detailStyleCenterAlignment; string currentTimeSpanRangeStr = getCurrentTimeSpanRangeStr(periodFromDate, dayIdx, empID); if (currentTimeSpanRangeStr != null) { detailCell.SetCellValue(currentTimeSpanRangeStr); } else { string currentRosterCodeDesc = getCurrentRosterCodeDesc(periodFromDate, dayIdx, empID); if (currentRosterCodeDesc != null) { detailCell.SetCellValue(currentRosterCodeDesc); } } string s = getCurrentLeaveCodeDesc(periodFromDate, dayIdx, empID); if (s != "") { detailCell2.SetCellValue(s); } } colpos += countDay; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(string.Empty); detailCell.CellStyle = detailStyleLast; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(string.Empty); detailCell2.CellStyle = detailStyleLast; }
public ActionResult ExceFill() { //创建一个ExCel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //获取集合的数据 List <ZhengZaiShangKeBiao> LIst = ShangKeBiaoBLL.GetAllData().OrderBy(p => p.RiQi).ToList(); //添加1个Sheet NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)book.CreateSheet("Sheet1"); //给sheet添加第一行的头部标题;获取工作表的第一行 NPOI.HSSF.UserModel.HSSFRow row1 = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(0); row1.CreateCell(0).SetCellValue("班级名称"); row1.CreateCell(1).SetCellValue("教室"); row1.CreateCell(2).SetCellValue("时间"); row1.CreateCell(3).SetCellValue("时间段"); row1.CreateCell(4).SetCellValue("阶段"); row1.CreateCell(5).SetCellValue("教员名称"); row1.CreateCell(6).SetCellValue("课程"); //将数据逐步写入到Sheet各个行 for (int i = 0; i < LIst.Count; i++) { //根据班级名查询出班级阶段 //int c; //if (LIst[i].JiaoYuanMingChen == "未安排") //{ // c = 0; //} //else //{ // c = Convert.ToInt32(LIst[i].JiaoYuanBianHao); //} //var a = yuan.GetAllData().Where(p => p.ID == c).ToList(); var ClassJD = BanJiBiaoBLL.GetAllData().Where(p => p.BanJiMing == LIst[i].ClassName).First(); //var JD = JiaoXueJieDuanBLL.GetAllData().Where(p => p.ID == ClassJD.JieDuanID).First(); //继续构造excel的数据行 NPOI.HSSF.UserModel.HSSFRow rowtemp = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(i + 1); var Class = KeChengJiHuaBiaoBLL.GetAllData().Where(p => p.BanJiMing == LIst[i].ClassName).First(); //var JD = JiaoXueJieDuanBLL.GetAllData().Where(p => p.ID == Class.KaiSheJiaoXueJieDuan).First(); rowtemp.CreateCell(0).SetCellValue(LIst[i].ClassName.ToString()); rowtemp.CreateCell(1).SetCellValue(LIst[i].ZiYuanMingChen.ToString()); rowtemp.CreateCell(2).SetCellValue(Convert.ToDateTime(LIst[i].RiQi).ToString("yyyy-MM-dd")); rowtemp.CreateCell(3).SetCellValue(LIst[i].ShiJianDuan.ToString()); rowtemp.CreateCell(4).SetCellValue(1); rowtemp.CreateCell(5).SetCellValue(LIst[i].JiaoYuanMingChen); rowtemp.CreateCell(6).SetCellValue(LIst[i].KeChengMingChen.ToString()); //继续添加列数据 } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); //Excel写入到内存中 book.Write(ms); //从0位置读到结束 ms.Seek(0, System.IO.SeekOrigin.Begin); Message(); //读取内存流中的二进制 byte[] bytes = ms.ToArray(); ms.Close(); ms.Dispose(); OutputCilent(bytes); return(Json(new { errorNo = "0", errorInfo = "查询成功" }, JsonRequestBehavior.AllowGet)); }
public void GenerateExcelReport(string exportFileName) { DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource(); int lastRowIndex = 0; // Set column style NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report"); // Date format NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Numeric format NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String left format NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String center format NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 0 style NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 10, 11 style NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 4 style NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 8 style NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column8Style.DataFormat = format.GetFormat("yyyy-MM-dd"); column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Align right NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Bottom border NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; // Bold style NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont(); boldFont.Boldweight = 700; boldStyle.SetFont(boldFont); // Header Border NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerBorderStyle.SetFont(boldFont); headerBorderStyle.WrapText = true; headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Left Border NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerLeftBorderStyle.SetFont(boldFont); headerLeftBorderStyle.WrapText = true; headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Right Border NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.SetFont(boldFont); headerRightBorderStyle.WrapText = true; headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Total numeric format NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; // Grey color NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; grey25Style.SetFont(boldFont); // Yellow color NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; yellowStyle.SetFont(boldFont); // Set column width worksheet.SetColumnWidth(0, 12 * 256); worksheet.SetColumnWidth(1, 20 * 256); worksheet.SetColumnWidth(2, 15 * 256); worksheet.SetColumnWidth(3, 15 * 256); worksheet.SetColumnWidth(4, 15 * 256); worksheet.SetColumnWidth(5, 7 * 256); worksheet.SetColumnWidth(6, 15 * 256); worksheet.SetColumnWidth(7, 15 * 256); worksheet.SetColumnWidth(8, 15 * 256); worksheet.SetColumnWidth(9, 15 * 256); worksheet.SetColumnWidth(10, 15 * 256); worksheet.SetColumnWidth(11, 25 * 256); // Set column title NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("KTPF Contribution Report"); headerCell.CellStyle = boldStyle; headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); if (dataSet.ORSOPlan.Rows.Count > 0) { Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0]; headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName); } headerCell.CellStyle = boldStyle; string m_reportPeriod = ""; if (_payPeriodFr.Month != _payPeriodTo.Month) { m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy"); } else { m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy"); } headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue(m_reportPeriod); headerCell.CellStyle = boldStyle; // Merge header headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3); // Merge cell from 5-8 NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("For New Joiner"); headerCell.CellStyle = yellowStyle; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); // Merge cell from 9-10 cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("For Resigned Staff"); headerCell.CellStyle = grey25Style; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4); headerRow.HeightInPoints = 40; // column A headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("Member ID"); headerCell.CellStyle = headerLeftBorderStyle; // column B headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1); headerCell.SetCellValue("Employee Name"); headerCell.CellStyle = headerBorderStyle; // column C headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2); headerCell.SetCellValue("Basic Salary"); headerCell.CellStyle = headerBorderStyle; // column D headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3); headerCell.SetCellValue("KTPF Contribution"); headerCell.CellStyle = headerBorderStyle; // column E headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4); headerCell.SetCellValue("Employer MPF Contribution"); headerCell.CellStyle = headerRightBorderStyle; // column F headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("Sex"); headerCell.CellStyle = headerBorderStyle; // column G headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6); headerCell.SetCellValue("Date Of Birth"); headerCell.CellStyle = headerBorderStyle; // column H headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7); headerCell.SetCellValue("Date Join"); headerCell.CellStyle = headerBorderStyle; // column I headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8); headerCell.SetCellValue("Effective Date"); headerCell.CellStyle = headerRightBorderStyle; // column J headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("Termination Date"); headerCell.CellStyle = headerBorderStyle; // column K headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10); headerCell.SetCellValue("Termination Mode"); headerCell.CellStyle = headerRightBorderStyle; // column L headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11); headerCell.SetCellValue("Remarks"); headerCell.CellStyle = headerRightBorderStyle; // Create total int length = dataSet.ExistingMember.Rows.Count + 5; NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length); NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1); totalCell.SetCellValue("Total"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2); totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3); totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4); totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; int rowLength = 0; // Set value for every row dataSet.ExistingMember.DefaultView.Sort = "EmpName"; DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable(); foreach (DataRow m_row in m_table.Rows) //foreach (DataRow row in tmpDataTable.Rows) { NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5); rowLength++; if (lastRowIndex == (m_table.Rows.Count)) { detailRow.RowStyle = bottomBorderStyle; } NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0); cell.SetCellValue(m_row["MemberID"].ToString()); cell.CellStyle = column0Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1); cell.SetCellValue(m_row["EmpName"].ToString()); cell.CellStyle = stringLeftStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2); cell.SetCellValue((double)m_row["RelevantIncome"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3); cell.SetCellValue((double)m_row["ER"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4); cell.SetCellValue((double)m_row["MpfMCER"]); cell.CellStyle = column4Style; DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"]; DateTime m_effDate = (DateTime)m_row["OrsoEffDate"]; { cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5); cell.CellStyle = stringCenterStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { cell.SetCellValue(m_row["EmpSex"].ToString()); } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDOB"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDateJoin"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8); cell.CellStyle = column8Style; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["OrsoEffDate"]); } catch { } } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9); cell.CellStyle = dateCellStyle; cell.SetCellValue("");// put something into the cell and so as the border line can be shown if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10); cell.SetCellValue(m_row["TermCode"].ToString()); cell.CellStyle = column10To11Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11); //cell.SetCellValue(row[FIELD_REMARKS].ToString()); cell.CellStyle = column10To11Style; lastRowIndex++; } System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); }
private void button13_Click(object sender, EventArgs e) { //清空报表 report1.ClearReport(); //设置报表为非只读 report1.ReadOnly = false; //报表停止公式计算,这样速度快点 report1.CalcFormula(false); //报表停止绘制 report1.AllowDraw(false); string fileName = Application.StartupPath + "\\Test.xls"; NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook; try { this.Cursor = Cursors.WaitCursor; using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0); if (sheet.LastRowNum == 0) { this.Cursor = Cursors.Arrow; return; } else { //产生列 NPOI.HSSF.UserModel.HSSFRow row = sheet.GetRow(0); //report1.AddColumn(row.Cells.Count); report1.AddColumn(row.LastCellNum); } int rowIndex = 0; System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { //增加行 rowIndex = report1.AddRow(Gscr.Band.Detail); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current; for (int i = 0; i < row.LastCellNum; i++) { Gscr.Cell cellRpt = report1[rowIndex, i]; NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(i); if (cell == null) { cellRpt.Value = null; } else { switch (cell.CellType) { case NPOI.HSSF.UserModel.HSSFCellType.BLANK: cellRpt.Value = null; break; case NPOI.HSSF.UserModel.HSSFCellType.BOOLEAN: cellRpt.Value = cell.BooleanCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.NUMERIC: cellRpt.Value = cell.ToString(); break; case NPOI.HSSF.UserModel.HSSFCellType.STRING: cellRpt.Value = cell.StringCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.ERROR: cellRpt.Value = cell.ErrorCellValue; break; default: break; } } } } } //报表恢复公式计算 report1.CalcFormula(true); //报表恢复绘制 report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("成功读取Excel文件【" + fileName + " 】。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception err) { report1.CalcFormula(true); report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("读取Excel文件失败。" + err.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Stop); } }
public void GenerateExcelReport(string _filename) { // F&V MPF Remittance Statement is composed of MPF and PFund data. So the current MPF Remittance Statement can only achieve 1/2 of their needs. if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null) { // get MPF part of the remmittance statement MPFFile.GenericMPFFile mpfFile = new MPFFile.GenericMPFFile(dbConn); mpfFile.LoadMPFFileDetail(EmpList, MPFPlanID, PayPeriodFr, PayPeriodTo); System.Data.DataSet dataSet = mpfFile.CreateRemittanceStatementDataSet(); string _schemeNo = ""; string _companyName = ""; DataTable m_table = null; NPOI.HSSF.UserModel.HSSFSheet m_worksheet = null; NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); if (this.MPFPlanID > 0) { EMPFPlan _mpfPlan = EMPFPlan.GetObject(dbConn, this.MPFPlanID); if (_mpfPlan != null) { EMPFScheme _mpfScheme = EMPFScheme.GetObject(dbConn, _mpfPlan.MPFSchemeID); if (_mpfScheme != null) { _schemeNo = _mpfScheme.MPFSchemeCode; } _companyName = _mpfPlan.MPFPlanCompanyName; } m_table = ConvertData(dataSet.Tables["NewJoinMember"], "", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-NewJoinMember"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = false", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-ExistingEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = true", 1); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-TerminatedEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1); } if (this.ORSOPlanID > 0) { EORSOPlan _orsoPlan = EORSOPlan.GetObject(dbConn, ORSOPlanID); if (_orsoPlan != null) { _schemeNo = _orsoPlan.ORSOPlanSchemeNo; _companyName = _orsoPlan.ORSOPlanCompanyName; } DataTable _orsoStaffTable = LoadORSODetail(); m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = false", 2); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-ExistingEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2); m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = true", 2); m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-TerminatedEmployee"); WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2); } // get ORSO part of remittance statement System.IO.FileStream file = new System.IO.FileStream(_filename, System.IO.FileMode.Create); workbook.Write(file); file.Close(); } }
protected override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet) { NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Allocation Report - Detail"); ushort rowPos = 0; DataTable empInfoTable = dataSet.Tables["EmpInfo"]; DataTable paymentTable = dataSet.Tables["payment"]; DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"]; DataTable hierarchyTable = dataSet.Tables["hierarchy"]; DataTable payPeriodTable = dataSet.Tables["payPeriod"]; DataTable hierarchyTotalTable = new DataTable("HierarchyTotal"); // use for calculate local hierarchy total hierarchyTotalTable.Columns.Add("company", typeof(string)); hierarchyTotalTable.Columns.Add("LevelDesc", typeof(string)); hierarchyTotalTable.Columns.Add("payPeriodFr", typeof(DateTime)); hierarchyTotalTable.Columns.Add("ee", typeof(double)); hierarchyTotalTable.Columns.Add("er", typeof(double)); hierarchyTotalTable.Columns.Add("netAmount", typeof(double)); NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont(); boldFont.Boldweight = 700; NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); reportHeaderStyle.SetFont(boldFont); reportHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); groupHeaderStyle.SetFont(boldFont); NPOI.HSSF.UserModel.HSSFCellStyle monthHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); monthHeaderStyle.SetFont(boldFont); monthHeaderStyle.DataFormat = workBook.CreateDataFormat().GetFormat("MMM-yyyy"); monthHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); columnHeaderStyle.SetFont(boldFont); columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle detailNumberStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); detailNumberStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)"); detailNumberStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; NPOI.HSSF.UserModel.HSSFCellStyle detailTextStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); detailTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); subTotalStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)"); subTotalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; DataRow[] EmpInfoRows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee No."); string currentCompany = "#$%@$@#$"; //string.Empty; string currentHierarchyGroup = string.Empty; string currentEmployeeNo = string.Empty; string tmpCompany = ""; string tmpHierarchy = ""; string tmpEmployeeNo = ""; ushort groupRowCount = 0; bool sectionEnded = false; if (bolShowIndividual == true) { summaryStart = Convert.ToUInt16(4 + hierarchyTable.Rows.Count); } else { summaryStart = 1; } reportEndCol = Convert.ToUInt16(summaryStart + (payPeriodTable.Rows.Count * 4) - 1); foreach (DataRow EmpInfoRow in EmpInfoRows) { tmpCompany = EmpInfoRow["Company"].ToString(); tmpEmployeeNo = EmpInfoRow["Employee No."].ToString(); tmpHierarchy = EmpInfoRow[hierarchyLevelGroupingFieldName].ToString(); if (EmpInfoRows[0] == EmpInfoRow) { currentHierarchyGroup = tmpHierarchy; currentCompany = tmpCompany; rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle); rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle); rowPos++; rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle); } sectionEnded = (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase) || !currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase)); if (sectionEnded) { if (bolShowIndividual == true) { // print hirarchy total if show employee detail rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount); rowPos += 2; } else { // just print hierarchy line (i.e. not subtotal) if employee detail not shown ushort colPos = 0; NPOI.HSSF.UserModel.HSSFRow sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell((int)colPos); cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos); cell.SetCellValue(currentHierarchyGroup); cell.CellStyle = detailTextStyle; colPos++; foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + currentHierarchyGroup + "' ", "payPeriodFr")) { cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["EE"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["ER"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")"; cell.CellStyle = detailNumberStyle; colPos++; } rowPos++; groupRowCount++; } if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase)) { if (bolShowIndividual == true) { // print company header and column header for new company (in show-employee mode) rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle); rowPos++; rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle); } else { // print a summary for previous company (in hide employee info mode) rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount); rowPos += 2; rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle); rowPos++; rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle); } currentCompany = tmpCompany; currentHierarchyGroup = tmpHierarchy; groupRowCount = 0; // print company information for new company } else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase)) { if (bolShowIndividual == true) { rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle); groupRowCount = 0; } else { } currentHierarchyGroup = tmpHierarchy; } hierarchyTotalTable.Clear(); } if (bolShowIndividual == true) { groupRowCount++; NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos); NPOI.HSSF.UserModel.HSSFCell cell; ushort colPos = 0; // Employee Number cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(EmpInfoRow["Employee No."].ToString()); cell.CellStyle = detailTextStyle; colPos++; // Employee Name cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(EmpInfoRow["Employee Name"].ToString()); cell.CellStyle = detailTextStyle; colPos++; // Alias cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(EmpInfoRow["Alias"].ToString()); cell.CellStyle = detailTextStyle; colPos++; // Chinese Name cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(EmpInfoRow["Chinese Name"].ToString()); cell.CellStyle = detailTextStyle; colPos++; // hierarchy foreach (DataRow m_hierarchyRow in hierarchyTable.Rows) { cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(EmpInfoRow[m_hierarchyRow["LevelDesc"].ToString()].ToString()); cell.CellStyle = detailTextStyle; colPos++; } foreach (DataRow m_payPeriodRow in payPeriodTable.Rows) { //DBFilter m_paymentFilter = new DBFilter(); //m_paymentFilter.add(new Match("payPeriodFr", m_payPeriodRow["payPeriodFr"])); //m_paymentFilter.add(new Match("EmpID", row["EmpID"])); double m_er = 0; double m_ee = 0; double m_netAmount = 0; foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " + "EmpID=" + EmpInfoRow["EmpID"].ToString())) { m_ee += (double)m_paymentRow["EE"]; m_er += (double)m_paymentRow["ER"]; m_netAmount += (double)m_paymentRow["netAmount"]; } cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(m_ee); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(m_netAmount); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(m_er); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")"; cell.CellStyle = detailNumberStyle; colPos++; } rowPos++; } else { foreach (DataRow m_payPeriodRow in payPeriodTable.Rows) { if (tmpCompany == "Magazines International (Asia) Limited - SALES" && tmpHierarchy == "Sales Division") { double m_trash = 0; } double m_er = 0; double m_ee = 0; double m_netAmount = 0; foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " + "EmpID=" + EmpInfoRow["EmpID"].ToString())) { m_ee += (double)m_paymentRow["EE"]; m_er += (double)m_paymentRow["ER"]; m_netAmount += (double)m_paymentRow["netAmount"]; } // find from local HierarchyTotal table and add the new employee amounts DataRow[] m_hierarchyTotalRows = hierarchyTotalTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " + // "Company='" + tmpCompany + "' AND " + "LevelDesc='" + tmpHierarchy + "' "); if (m_hierarchyTotalRows.Length > 0) { m_hierarchyTotalRows[0]["EE"] = (double)m_hierarchyTotalRows[0]["EE"] + m_ee; m_hierarchyTotalRows[0]["ER"] = (double)m_hierarchyTotalRows[0]["ER"] + m_er; m_hierarchyTotalRows[0]["netAmount"] = (double)m_hierarchyTotalRows[0]["netAmount"] + m_netAmount; } else { DataRow m_newHierarchyTotal = hierarchyTotalTable.Rows.Add(); m_newHierarchyTotal["company"] = tmpCompany; m_newHierarchyTotal["LevelDesc"] = tmpHierarchy; m_newHierarchyTotal["payPeriodFr"] = (DateTime)m_payPeriodRow["payPeriodFr"]; m_newHierarchyTotal["EE"] = m_ee; m_newHierarchyTotal["ER"] = m_er; m_newHierarchyTotal["netAmount"] = m_netAmount; } } } } // insert last hierarchy total if (bolShowIndividual == true) { rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount); } else { NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(0); ushort colPos = 0; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue(tmpHierarchy); cell.CellStyle = detailTextStyle; colPos++; foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + tmpHierarchy + "' ", "payPeriodFr")) { cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["EE"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.SetCellValue((double)m_hiearchyTotalRow["ER"]); cell.CellStyle = detailNumberStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos); cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")"; cell.CellStyle = detailNumberStyle; colPos++; } groupRowCount++; rowPos++; rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount); } for (int i = 0; i <= reportEndCol; i++) { if (i < summaryStart) { workSheet.SetColumnWidth(i, 15 * 254); } else { workSheet.SetColumnWidth(i, 14 * 254); } } }
private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName) { int columnCount = 0; int lastRowIndex = 0; // Set column style NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport"); NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00"); numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Set column width worksheet.SetColumnWidth(0, 40 * 256); worksheet.SetColumnWidth(2, 20 * 256); worksheet.SetColumnWidth(5, 15 * 256); worksheet.SetColumnWidth(7, 15 * 256); worksheet.SetColumnWidth(8, 15 * 256); worksheet.SetColumnWidth(9, 15 * 256); worksheet.SetColumnWidth(10, 12 * 256); // Set column title NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex); headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export"); headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1); headerRow.CreateCell(0).SetCellValue(peroid); headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3); foreach (DataColumn headercolumn in tmpDataTable.Columns) { NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); cell.SetCellValue(headercolumn.ColumnName); if (columnCount == 11) { cell.CellStyle = style; } columnCount++; } // Set value for every row foreach (DataRow row in tmpDataTable.Rows) { NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4); detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString()); detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString()); detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString()); detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString()); detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString()); detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString()); detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString()); detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString()); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8); cell.SetCellValue((DateTime)row[FIELD_FROM]); cell.CellStyle = dateCellStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9); cell.SetCellValue((DateTime)row[FIELD_TO]); cell.CellStyle = dateCellStyle; detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString()); cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11); cell.SetCellValue((double)row[FIELD_PERCENTAGE]); cell.CellStyle = numericStyle; lastRowIndex++; } System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); }
protected ushort GenerateColumnHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet, string HierarchyHeader, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle MonthHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ColumnHeaderStyle) { DataTable paymentTable = dataSet.Tables["payment"]; DataTable hierarchyTable = dataSet.Tables["hierarchy"]; DataTable payPeriodTable = dataSet.Tables["payPeriod"]; NPOI.HSSF.UserModel.HSSFRow monthRow; NPOI.HSSF.UserModel.HSSFRow columnHeaderRow; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0); //workSheet.Cells.Add((int)RowPos, 1, HierarchyHeader, xf); monthRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); RowPos++; columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos); ushort colPos = 0; if (bolShowIndividual == true) { cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("Employee No."); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("English Name"); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("Alias"); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("Chinese Name"); cell.CellStyle = ColumnHeaderStyle; foreach (DataRow hierarchyRow in hierarchyTable.Rows) { colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue(hierarchyRow["LevelDesc"].ToString()); cell.CellStyle = ColumnHeaderStyle; } } else { cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue(HierarchyHeader); cell.CellStyle = ColumnHeaderStyle; } summaryStart = Convert.ToUInt16(colPos + Convert.ToUInt16(1)); foreach (DataRow payPeriodRow in payPeriodTable.Rows) { colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)monthRow.CreateCell(colPos); cell.SetCellValue(DateTime.Parse(payPeriodRow["payPeriodFr"].ToString()).ToString("dd-MM-yyyy")); cell.CellStyle = MonthHeaderStyle; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(monthRow.RowNum, monthRow.RowNum, colPos, colPos + 3)); cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("MPF EE"); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("Net Payment"); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("MPF ER"); cell.CellStyle = ColumnHeaderStyle; colPos++; cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos); cell.SetCellValue("Total"); cell.CellStyle = ColumnHeaderStyle; } RowPos++; return(RowPos); }
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword) { ClearTempTable(); NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Filename, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); NPOI.HSSF.UserModel.HSSFSheet workSheet = null; //foreach (ExcelLibrary.SpreadSheet.Worksheet tmpWorkSheet in workBook.Worksheets) //{ // if (tmpWorkSheet.Name.Trim().Equals("RosterTable")) // { // workSheet = tmpWorkSheet; // break; // } //} try { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheet("RosterTable"); if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } catch { if (workSheet == null) { workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0); } } if (workSheet.GetRow(ROW_YEAR).GetCell(0).StringCellValue.Trim().StartsWith("Year", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_YEAR).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importYear = Convert.ToInt32(workSheet.GetRow(ROW_YEAR).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Year", null); } } if (workSheet.GetRow(ROW_MONTH).GetCell(0).StringCellValue.Trim().StartsWith("Month", StringComparison.CurrentCultureIgnoreCase)) { if (workSheet.GetRow(ROW_MONTH).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { m_importMonth = Convert.ToInt32(workSheet.GetRow(ROW_MONTH).GetCell(1).NumericCellValue); } else { errors.addError("Invalid Month", null); } //if (!int.TryParse(workSheet.GetRow(1).GetCell(1).StringCellValue.Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); } if (errors.List.Count > 0) { throw (new HRImportException(errors.Message())); } int intHeaterRow = ROW_CALENDAR_HEADER; int intEmpColumn = 0; ArrayList results = new ArrayList(); int rowCount = 1; NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow); while (intEmpColumn <= headerRow.LastCellNum) { if (headerRow.GetCell(intEmpColumn) != null) { if (headerRow.GetCell(intEmpColumn).StringCellValue.Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) { break; } } intEmpColumn++; } if (intEmpColumn > headerRow.LastCellNum) { // do exception } int intEmptyEmpNoCount = 0; try { while (intHeaterRow + rowCount <= workSheet.LastRowNum) { int EmpID = 0; string EmpNo = string.Empty; int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow + rowCount); if (row == null) { rowCount++; intEmptyEmpNoCount++; continue; } if (row.GetCell(intEmpColumn) == null) { rowCount++; intEmptyEmpNoCount++; continue; } EmpNo = row.GetCell(intEmpColumn).ToString().Trim(); if (string.IsNullOrEmpty(EmpNo)) { rowCount++; intEmptyEmpNoCount++; continue; } intEmptyEmpNoCount = 0; EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); if (EmpID < 0) { errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } else if (EmpID == 0) { errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); } colCount = intEmptyEmpNoCount + 1; int lastImportDay = 0; int intCurrentMonth = m_importMonth; int intCurrentYear = m_importYear; while (colCount < headerRow.LastCellNum) { if (headerRow.GetCell(colCount) == null) { if (lastImportDay <= 0) { colCount++; continue; } else { break; } } if (headerRow.GetCell(colCount).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { int day = Convert.ToInt32(headerRow.GetCell(colCount).NumericCellValue); if (day > 0 && day <= DateTime.DaysInMonth(intCurrentYear, intCurrentMonth)) { if (lastImportDay > day) { intCurrentMonth++; if (intCurrentMonth > 12) { intCurrentMonth = 1; intCurrentYear++; } } lastImportDay = day; string RosterCode; if (row.GetCell(colCount) != null) { RosterCode = row.GetCell(colCount).ToString().Trim(); } else { RosterCode = string.Empty; } EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); uploadRosterTable.EmpID = EmpID; uploadRosterTable.RosterTableDate = new DateTime(intCurrentYear, intCurrentMonth, day); DateTime inTime, outTime; uploadRosterTable.RosterCodeID = Import.Parse.GetRosterCodeID(dbConn, RosterCode, out inTime, out outTime); if (!inTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideInTime = inTime; } if (!outTime.Ticks.Equals(0)) { uploadRosterTable.RosterTableOverrideOutTime = outTime; } uploadRosterTable.SessionID = m_SessionID; uploadRosterTable.TransactionDate = UploadDateTime; uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) { errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); } else { EUploadRosterTable.db.insert(dbConn, uploadRosterTable); } } } colCount++; } rowCount++; } } catch (Exception e) { errors.addError(e.Message, null); } if (errors.List.Count > 0) { ClearTempTable(); throw (new HRImportException(errors.Message())); } return(GetImportDataFromTempDatabase(null)); //org.in2bits.MyXls.XlsDocument xlsDoc = new org.in2bits.MyXls.XlsDocument(Filename); //org.in2bits.MyXls.Worksheet workSheet; //try //{ // workSheet = xlsDoc.Workbook.Worksheets["RosterTable"]; //} //catch //{ // workSheet = xlsDoc.Workbook.Worksheets[0]; //} //if (workSheet.Rows[1].GetCell(1).Value.ToString().Trim().Equals("Year", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[1].CellAtCol(2).Value.ToString().Trim(), out m_importYear)) // errors.addError("Invalid Year", null); //} //if (workSheet.Rows[2].GetCell(1).Value.ToString().Trim().Equals("Month", StringComparison.CurrentCultureIgnoreCase)) //{ // if (!int.TryParse(workSheet.Rows[2].CellAtCol(2).Value.ToString().Trim(), out m_importMonth)) // errors.addError("Invalid Month", null); //} //if (errors.List.Count > 0) //{ // throw (new HRImportException(errors.Message())); //} //ushort intHeaderRow = 3; //ArrayList results = new ArrayList(); //ushort rowCount = 1; //try //{ // while (intHeaderRow + rowCount <= workSheet.Rows.MaxRow) // { // int EmpID = 0; // string EmpNo = string.Empty; // ushort colCount = 1; // org.in2bits.MyXls.Row row = workSheet.Rows[(ushort)(intHeaderRow + rowCount)]; // while (colCount <= row.MaxCellCol) // { // if (workSheet.Rows[intHeaderRow].CellAtCol(colCount).Value.ToString().Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase)) // { // EmpNo = row.CellAtCol(colCount).Value.ToString().Trim(); // EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID); // if (EmpID <= 0) // errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() }); // break; // } // colCount++; // } // colCount = 1; // while (colCount <= workSheet.Rows[(ushort)(intHeaderRow + rowCount)].MaxCellCol) // { // if (workSheet.Cells .Rows[(ushort)(intHeaderRow)].CellExists(colCount)) // { // string RosterCode = string.Empty; // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellExists(colCount)) // if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellAtCol(colCount).Value != null) // RosterCode = row.CellAtCol(colCount).Value.ToString().Trim(); // int day = 0; // if (int.TryParse(workSheet.Rows[intHeaderRow].GetCell(colCount).Value.ToString().Trim(), out day)) // { // if (day > 0 && day <= DateTime.DaysInMonth(m_importYear, m_importMonth)) // { // EUploadRosterTable uploadRosterTable = new EUploadRosterTable(); // uploadRosterTable.EmpID = EmpID; // uploadRosterTable.RosterTableDate = new DateTime(m_importYear, m_importMonth, day); // uploadRosterTable.RosterCodeID = Import.Parse.*ID(dbConn, RosterCode); // uploadRosterTable.SessionID = m_SessionID; // uploadRosterTable.TransactionDate = UploadDateTime; // uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE; // if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode)) // { // errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() }); // } // else // EUploadRosterTable.db.insert(dbConn, uploadRosterTable); // } // } // } // colCount++; // } // rowCount++; // } //} //catch (Exception e) //{ // errors.addError(e.Message, null); //} //if (errors.List.Count > 0) //{ // ClearTempTable(); // throw (new HRImportException(errors.Message())); //} //return GetImportDataFromTempDatabase(null); }
public ActionResult ExportMsg() { List <String> list = new List <String>(); List <String> listName = new List <String>(); #region 初始化数据 //得到选中项 这是比较笨的方法,有机会要改进哈 if (Request.Form["StuNum"] != null) { list.Add("StuNum"); listName.Add("学号"); } if (Request.Form["StuName"] != null) { list.Add("StuName"); listName.Add("姓名"); } if (Request.Form["Gender"] != null) { list.Add("Gender"); listName.Add("性别"); } if (Request.Form["QQNum"] != null) { list.Add("QQNum"); listName.Add("QQ号"); } if (Request.Form["Email"] != null) { list.Add("Email"); listName.Add("电子邮箱"); } if (Request.Form["Birthday"] != null) { list.Add("Birthday"); listName.Add("生日"); } if (Request.Form["Class"] != null) { list.Add("Class"); listName.Add("班级"); } if (Request.Form["TelephoneNumber"] != null) { list.Add("TelephoneNumber"); listName.Add("电话号码"); } if (Request.Form["Department"] != null) { list.Add("Department"); listName.Add("部门"); } if (Request.Form["TechnicalGuideNumber"] != null) { list.Add("TechnicalGuideNumber"); listName.Add("技术指导号"); } if (Request.Form["JoinTime"] != null) { list.Add("JoinTime"); listName.Add("加入时间"); } if (Request.Form["Major"] != null) { list.Add("Major"); listName.Add("主修"); } if (Request.Form["Counseloer"] != null) { list.Add("Counseloer"); listName.Add("辅导员"); } if (Request.Form["HeadTeacher"] != null) { list.Add("HeadTeacher"); listName.Add("班主任"); } if (Request.Form["UndergraduateTutor"] != null) { list.Add("UndergraduateTutor"); listName.Add("毕业导师"); } if (Request.Form["HomPhoneNumber"] != null) { list.Add("HomPhoneNumber"); listName.Add("家庭电话"); } if (Request.Form["FamilyAddress"] != null) { list.Add("FamilyAddress"); listName.Add("家庭地址"); } if (Request.Form["Sign"] != null) { list.Add("Sign"); listName.Add("个性签名"); } int depart = 0; if (!string.IsNullOrEmpty((Request.Form["depart"]))) { depart = Convert.ToInt32(Request.Form["depart"]); } if (list.Count == 0) { return(Content("<script>alert('您没有选择任何项喔~~!');window.location='/PersonalManger/CheckMember/index'</script>")); } DataTable dt = OperateContext.Current.BLLSession.IMemberInformationBLL.GetPartData(list, depart); //如果选择了部门那么导出时要将数字转为部门名 int count = 0; if (Request.Form["Department"] != null) { for (int i = 0; i < list.Count; i++) { if (list[i] == "Department") { count = i; } } } #endregion NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet sheet = workbook.CreateSheet("第一页") as NPOI.HSSF.UserModel.HSSFSheet; NPOI.HSSF.UserModel.HSSFRow hr = sheet.CreateRow(0) as NPOI.HSSF.UserModel.HSSFRow; for (int i = 0; i < listName.Count; i++) { hr.CreateCell(i).SetCellValue(listName[i]); } for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++) { NPOI.HSSF.UserModel.HSSFRow hro = sheet.CreateRow(rowIndex + 1) as NPOI.HSSF.UserModel.HSSFRow; for (int colIndex = 0; colIndex < listName.Count; colIndex++) { string str = ""; if (colIndex == count && count != 0) { if (dt.Rows[rowIndex][colIndex].ToString() == "10001") { str = "NET应用开发部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10002") { str = "安卓应用开发部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10003") { str = "硬件编程技术部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10004") { str = "系统编程技术部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10007") { str = "暂未加入部门"; } hro.CreateCell(colIndex).SetCellValue(str); } else { hro.CreateCell(colIndex).SetCellValue(dt.Rows[rowIndex][colIndex].ToString()); } } } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel")); }