public int getRowCount(String sheetName) { int index = Workbook.GetSheetIndex(sheetName); if (index == -1) { return(0); } else { sheet = (HSSFSheet)Workbook.GetSheetAt(index); int number = sheet.LastRowNum + 1; return(number); } }
public void SetCellFormula(String formula) { if (IsPartOfArrayFormulaGroup) { NotifyArrayFormulaChanging(); } int row = _record.Row; int col = _record.Column; short styleIndex = _record.XFIndex; if (string.IsNullOrEmpty(formula)) { NotifyFormulaChanging(); SetCellType(CellType.Blank, false, row, col, styleIndex); return; } int sheetIndex = book.GetSheetIndex(_sheet); Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.Cell, sheetIndex); SetCellType(CellType.Formula, false, row, col, styleIndex); FormulaRecordAggregate agg = (FormulaRecordAggregate)_record; FormulaRecord frec = agg.FormulaRecord; frec.Options = ((short)2); frec.Value = (0); //only set to default if there is no extended format index already set if (agg.XFIndex == (short)0) { agg.XFIndex = ((short)0x0f); } agg.SetParsedExpression(ptgs); }
public void TestBug50298() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); ISheet sheet = wb.CloneSheet(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)"); wb.SetSheetName(wb.GetSheetIndex(sheet), "copy"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy"); wb.SetSheetOrder("copy", 0); assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received"); wb.RemoveSheetAt(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); // check that the overall workbook serializes with its correct size int expected = wb.Workbook.Size; int written = wb.Workbook.Serialize(0, new byte[expected * 2]); Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected); HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb); assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); }
/// <summary> /// Export the files to CSV format from the file menu. /// </summary> /// <param name="sender">A reference to the control/object that raised the event.</param> /// <param name="e">State information and event data associated with a routed event.</param> private void Export_Click(object sender, RoutedEventArgs e) { if (ViewModel.Conflicts.Count != 0) { string message = "Exporting to Excel while there are conflicts may result in incorrect output. Do you wish to continue with the export?"; string caption = "Export to Excel"; MessageBoxImage icon = MessageBoxImage.Warning; MessageBoxButton button = MessageBoxButton.YesNo; MessageBoxResult result = System.Windows.MessageBox.Show(message, caption, button, icon); if (result == MessageBoxResult.No) { return; } } Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog(); saveFileDialog.Filter = "Excel Worksheets|*.xls"; if (saveFileDialog.ShowDialog() == true) { var fileName = saveFileDialog.FileName; var templateFile = System.IO.Path.Combine(Environment.CurrentDirectory, "ClassroomGridTemplate.xls"); using (var fileStream = File.OpenRead(templateFile)) { IWorkbook workbook = new HSSFWorkbook(fileStream); workbook.RemoveSheetAt(workbook.GetSheetIndex("Sheet1")); workbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK; ExcelSchedulePrinter printer = new ExcelSchedulePrinter(fileName, workbook); ICourseRepository courseRepository = CourseRepository.GetInstance(); new ScheduleVisualization(courseRepository, null, printer, "", null).PrintSchedule(); } } }
public static int getRowCount(String sheetName) { int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(0); } else { sheet = workbook.GetSheetAt(index); Console.WriteLine("sheet 1" + sheet); int number = sheet.LastRowNum + 1; return(number); } }
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook, CellRangeAddressList addressList, string formulaName) { var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss"); ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName); workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true); //CourseSheet.CreateRow(0).CreateCell(0).SetCellValue(""); IRow row = null; ICell cell = null; for (int i = 0; i < datas.Length; i++) { row = CourseSheet.CreateRow(i); cell = row.CreateCell(0); cell.SetCellValue(datas[i]); } IName range = workbook.CreateName(); range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length); range.NameName = formulaName; DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName); HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint); sheet.AddValidationData(dataValidate); }
/// <summary> /// 增加一個新的表格,並將新增的表格作為活動表 /// </summary> /// <param name="sheetName">表名稱</param> public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex) { int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName)); _currentSheet = _workbook.CloneSheet(i); sheetIndex = _workbook.GetSheetIndex(_currentSheet); _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName); _defaultExcelCellStyle = new ExcelCellStyle(); }
public void TestBug57231() { HSSFWorkbook wb = HSSFTestDataSamples .OpenSampleWorkbook("57231_MixedGasReport.xls"); HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet; HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet; int idx = wb.GetSheetIndex(newSheet); wb.SetSheetName(idx, "newName"); // Write the output to a file HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.IsNotNull(wbBack); Assert.IsNotNull(wbBack.GetSheet("master")); Assert.IsNotNull(wbBack.GetSheet("newName")); }
public void TestBug58085RemoveSheetWithNames() { HSSFWorkbook wb1 = new HSSFWorkbook(); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); ISheet sheet3 = wb1.CreateSheet("sheet3"); sheet1.CreateRow(0).CreateCell((short)0).SetCellValue("val1"); sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("val2"); sheet3.CreateRow(0).CreateCell((short)0).SetCellValue("val3"); IName namedCell1 = wb1.CreateName(); namedCell1.NameName = (/*setter*/ "name1"); String reference1 = "sheet1!$A$1"; namedCell1.RefersToFormula = (/*setter*/ reference1); IName namedCell2 = wb1.CreateName(); namedCell2.NameName = (/*setter*/ "name2"); String reference2 = "sheet2!$A$1"; namedCell2.RefersToFormula = (/*setter*/ reference2); IName namedCell3 = wb1.CreateName(); namedCell3.NameName = (/*setter*/ "name3"); String reference3 = "sheet3!$A$1"; namedCell3.RefersToFormula = (/*setter*/ reference3); HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1); wb1.Close(); IName nameCell = wb2.GetName("name1"); Assert.AreEqual("sheet1!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.RemoveSheetAt(wb2.GetSheetIndex("sheet1")); nameCell = wb2.GetName("name1"); Assert.AreEqual("#REF!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name2"); Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula); nameCell = wb2.GetName("name3"); Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula); wb2.Close(); }
/// <summary> /// 引用另一个工作表的形式 得到下拉 /// </summary> /// <param name="book"></param> /// <param name="columnIndex"></param> /// <param name="values"></param> /// <param name="sheetName"></param> /// <returns></returns> public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "") { if (values == null) { return; } if (string.IsNullOrEmpty(sheetName)) { sheetName = "_constraintSheet_"; } //创建下拉数据到新表中 ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);; var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = string.Empty; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果无可选值的话,则增加一个空选项,防止用户填写内容 if (values.Count() == 0) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(" "); rowIndex++; } //给该列所有单元格加上下拉选择 IName range = book.CreateName(); range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(), sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN); //创建验证 HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint); //关联验证 HSSFSheet v = book.GetSheetAt(0) as HSSFSheet; v.AddValidationData(valid); }
public void TestPlainValueCache() { HSSFWorkbook wb = new HSSFWorkbook(); int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets IRow row; ICell cell; //create summary sheet ISheet summary = wb.CreateSheet("summary"); wb.SetActiveSheet(wb.GetSheetIndex(summary)); //formula referring all sheets Created below row = summary.CreateRow(0); ICell summaryCell = row.CreateCell(0); summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")"; //create sheets with cells having (different) numbers // and add a row to summary for (int i = 1; i < numberOfSheets; i++) { ISheet sheet = wb.CreateSheet("new" + i); row = sheet.CreateRow(0); cell = row.CreateCell(0); cell.SetCellValue(i); row = summary.CreateRow(i); cell = row.CreateCell(0); cell.CellFormula = "new" + i + "!A1"; } //calculate IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Evaluator.EvaluateFormulaCell(summaryCell); Assert.AreEqual(8394753.0, summaryCell.NumericCellValue); }
public void TestGetSheetIndex() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1"); HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2"); HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3"); HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4"); Assert.AreEqual(0, wb.GetSheetIndex(sheet1)); Assert.AreEqual(1, wb.GetSheetIndex(sheet2)); Assert.AreEqual(2, wb.GetSheetIndex(sheet3)); Assert.AreEqual(3, wb.GetSheetIndex(sheet4)); // remove sheets wb.RemoveSheetAt(0); wb.RemoveSheetAt(2); // ensure that sheets are Moved up and Removed sheets are not found any more Assert.AreEqual(-1, wb.GetSheetIndex(sheet1)); Assert.AreEqual(0, wb.GetSheetIndex(sheet2)); Assert.AreEqual(1, wb.GetSheetIndex(sheet3)); Assert.AreEqual(-1, wb.GetSheetIndex(sheet4)); }
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values) { var sheetName = "_constraintSheet_"; ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName); var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = ""; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果只有一个可选值,则增加一个相同的选项 if (values.Count() == 1) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(lastValue); rowIndex++; } IName range = book.CreateName(); range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(), sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), true); return(new HSSFDataValidation(cellRegions, constraint)); }
public void SalvarDataTable(DataTable dt, string path) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //Criar uma tabela em branco ISheet sheet = hssfworkbook.CreateSheet(); //Colocar dados nela for (int i = 0; i < dt.Rows.Count; i++) { sheet.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } hssfworkbook.CreateSheet(); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), sheet.SheetName); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; //Write the stream data of workbook to the root directory FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets) { IRow row; ICell cell; //create summary sheet ISheet summary = wb.CreateSheet("summary"); wb.SetActiveSheet(wb.GetSheetIndex(summary)); //formula referring all sheets Created below row = summary.CreateRow(0); ICell summaryCell = row.CreateCell(0); summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")"); //create sheets with cells having (different) numbers // and add a row to summary for (int i = 1; i < numberOfSheets; i++) { ISheet sheet = wb.CreateSheet("new" + i); row = sheet.CreateRow(0); cell = row.CreateCell(0); cell.SetCellValue(i); row = summary.CreateRow(i); cell = row.CreateCell(0); cell.CellFormula = ("new" + i + "!A1"); } //calculate IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Evaluator.EvaluateFormulaCell(summaryCell); }
private void Menu_Export(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel Worksheets|*.xls"; if (saveFileDialog.ShowDialog() == true) { var fileName = saveFileDialog.FileName; var templateFile = Path.Combine(Environment.CurrentDirectory, "ClassroomGridTemplate.xls"); using (var fileStream = File.OpenRead(templateFile)) { IWorkbook workbook = new HSSFWorkbook(fileStream); workbook.RemoveSheetAt(workbook.GetSheetIndex("Sheet1")); workbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK; ExcelSchedulePrinter printer = new ExcelSchedulePrinter(fileName, workbook); ICourseRepository courseRepository = InMemoryCourseRepository.getInstance(); //IRoomRepository roomRepository = InMemoryRoomRepository.getInstance(); new ScheduleVisualization(courseRepository, null, printer).PrintSchedule(); } } }
public ActionResult Generar(DateTime Desde, DateTime Hasta, Guid?Id_Parq) { try { Utilidades.Export export = new Utilidades.Export(); FileStream fs = new FileStream(Server.MapPath(@"~\Archivos\NPOI.xls"), FileMode.Open, FileAccess.Read); Parqueadero parqueadero = db.Parqueaderoes.Find(Id_Parq); // Getting the complete workbook... HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true); NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = parqueadero.NombreEmpresa_Parq; dsi.Manager = parqueadero.NombreEmpresa_Parq; templateWorkbook.DocumentSummaryInformation = dsi; // Getting the worksheet by its name... HSSFSheet sheet = templateWorkbook.GetSheet("Hoja1") as HSSFSheet; DataSet dts = new DataSet(); DataTable dtb = new DataTable(); dts = export.ConvertDataSet(reportes(Desde, Hasta, Id_Parq.Value).ToList(), dts); if (dts.Tables.Count > 0) { dtb = dts.Tables[0]; } int fila = 11, columna = 0, i = 0; foreach (DataRow item in dtb.Rows) { HSSFRow dataRow = null; IRow row = null; dataRow = sheet.GetRow(fila) as HSSFRow; if (dataRow == null) { row = sheet.CreateRow(fila); dataRow = sheet.GetRow(fila) as HSSFRow; if (dataRow == null) { throw new Exception("ha ocurrido un error al crear el archivo .xls"); } } //ArregloExcel(item); foreach (var item1 in item.ItemArray) { if (i == 0) { if (row != null && row.Count() <= 27) { row.CreateCell(columna); } if (!string.IsNullOrEmpty(item1.ToString())) { dataRow.GetCell(columna).SetCellValue(string.Format(item1.ToString())); } else { dataRow.GetCell(columna).SetCellValue(string.Format("N/A")); } } else { if (row != null && row.Count() <= 27) { row.CreateCell(columna); } if (!string.IsNullOrEmpty(item1.ToString())) { dataRow.GetCell(columna).SetCellValue(item1.ToString()); } else { dataRow.GetCell(columna).SetCellValue(string.Format("N/A")); } } // Setting the value 77 at row 5 column 1 columna++; i++; } fila++; columna = 0; i = 0; } //GraficaExcel(templateWorkbook, dtb); MemoryStream ms = new MemoryStream(); string fechas = ("(" + Desde.ToString("yyyy-MM-dd") + "-" + Hasta.ToString("yyyy-MM-dd") + ")"); templateWorkbook.SetSheetName(templateWorkbook.GetSheetIndex(sheet), fechas); // Writing the workbook content to the FileStream... templateWorkbook.Write(ms); // Sending the server processed data back to the user computer... return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Reportes" + fechas + ".xls"))); } catch (Exception) { throw; } }
public void TestBackupRecord() { HSSFWorkbook wb = new HSSFWorkbook(); for (int i = 0; i < 10; i++) { HSSFSheet s = (HSSFSheet)wb.CreateSheet("Sheet " + i); InternalSheet sheet = s.Sheet; } // Check the initial order Assert.AreEqual(0, wb.GetSheetIndex("Sheet 0")); Assert.AreEqual(1, wb.GetSheetIndex("Sheet 1")); Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2")); Assert.AreEqual(3, wb.GetSheetIndex("Sheet 3")); Assert.AreEqual(4, wb.GetSheetIndex("Sheet 4")); Assert.AreEqual(5, wb.GetSheetIndex("Sheet 5")); Assert.AreEqual(6, wb.GetSheetIndex("Sheet 6")); Assert.AreEqual(7, wb.GetSheetIndex("Sheet 7")); Assert.AreEqual(8, wb.GetSheetIndex("Sheet 8")); Assert.AreEqual(9, wb.GetSheetIndex("Sheet 9")); // Change wb.Workbook.SetSheetOrder("Sheet 6", 0); wb.Workbook.SetSheetOrder("Sheet 3", 7); wb.Workbook.SetSheetOrder("Sheet 1", 9); // Check they're currently right Assert.AreEqual(0, wb.GetSheetIndex("Sheet 6")); Assert.AreEqual(1, wb.GetSheetIndex("Sheet 0")); Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2")); Assert.AreEqual(3, wb.GetSheetIndex("Sheet 4")); Assert.AreEqual(4, wb.GetSheetIndex("Sheet 5")); Assert.AreEqual(5, wb.GetSheetIndex("Sheet 7")); Assert.AreEqual(6, wb.GetSheetIndex("Sheet 3")); Assert.AreEqual(7, wb.GetSheetIndex("Sheet 8")); Assert.AreEqual(8, wb.GetSheetIndex("Sheet 9")); Assert.AreEqual(9, wb.GetSheetIndex("Sheet 1")); // Read it in and see if it is correct. MemoryStream baos = new MemoryStream(); wb.Write(baos); MemoryStream bais = new MemoryStream(baos.ToArray()); HSSFWorkbook wbr = new HSSFWorkbook(bais); Assert.AreEqual(0, wbr.GetSheetIndex("Sheet 6")); Assert.AreEqual(1, wbr.GetSheetIndex("Sheet 0")); Assert.AreEqual(2, wbr.GetSheetIndex("Sheet 2")); Assert.AreEqual(3, wbr.GetSheetIndex("Sheet 4")); Assert.AreEqual(4, wbr.GetSheetIndex("Sheet 5")); Assert.AreEqual(5, wbr.GetSheetIndex("Sheet 7")); Assert.AreEqual(6, wbr.GetSheetIndex("Sheet 3")); Assert.AreEqual(7, wbr.GetSheetIndex("Sheet 8")); Assert.AreEqual(8, wbr.GetSheetIndex("Sheet 9")); Assert.AreEqual(9, wbr.GetSheetIndex("Sheet 1")); // Now get the index by the sheet, not the name for (int i = 0; i < 10; i++) { NPOI.SS.UserModel.ISheet s = wbr.GetSheetAt(i); Assert.AreEqual(i, wbr.GetSheetIndex(s)); } }
/// <summary> /// 读取excel2003 /// 当string.IsNullOrEmpty(sheetName)为TRUE时,则按sheetIndex读取 /// 否则,按sheetName读取 /// </summary> /// <param name="Content"></param> /// <returns></returns> public DataTable GetExcelToTable03(Stream Content, string sheetName, int sheetIndex = 0) //2003 Excel { HSSFWorkbook workbook = new HSSFWorkbook(Content); //获取excel的第一个sheet HSSFSheet sheet; if (string.IsNullOrEmpty(sheetName)) { sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex); } else { int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(null); } sheet = (HSSFSheet)workbook.GetSheet(sheetName); } DataTable table = new DataTable(); try { //获取sheet的首行 HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } //最后一列的标号 即总的行数 int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); if (row == null || row.Cells.Count == 0) { continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } table.Rows.Add(dataRow); } workbook = null; sheet = null; } catch (Exception ex) { FileLog.WriteLog("Exception: " + ex); table = null; } return(table); }
/// <summary> ///從datatable中導出到excel /// </summary> /// <param name="strFileName"> excel文件名</param> /// <param name="dtSource"> datatabe源數據</param> /// <param name="strHeaderText">表名</param> /// <param name="sheetnum"> sheet的編號</param> /// <returns></returns> static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary <string, string> dir, int sheetnum) { //創建工作簿和sheet IWorkbook workbook = new HSSFWorkbook(); using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read)) { if (writefile.Length > 0 && sheetnum > 0) { workbook = WorkbookFactory.Create(writefile); } } ISheet sheet = null; ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd "); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表頭,填充列頭,樣式 if (rowIndex == 0) { string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString()); if (workbook.GetSheetIndex(sheetName) >= 0) { workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName)); } sheet = workbook.CreateSheet(sheetName); #region 表頭及樣式 { sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; rowIndex = 1; } #endregion #region 列頭及樣式 if (rowIndex == 1) { IRow headerRow = sheet.CreateRow(1); //第二行設置列名 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //寫入列標題 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //設置列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2); } rowIndex = 2; } #endregion } #endregion #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case " System.String ": //字符串類型 double result; if (isNumeric(drValue, out result)) { //數字字符串 double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case " System.DateTime ": //日期類型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化顯示 break; case " System.Boolean ": //布爾型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case " System.Int16 ": //整型 case " System.Int32 ": case " System.Int64 ": case " System.Byte ": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case " System.Decimal ": //浮點型 case " System.Double ": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case " System.DBNull ": //空值處理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(drValue.ToString()); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// 生成excel /// </summary> /// <param name="templatePath">模板路径</param> /// <param name="saveFolder">保存文件夹路径</param> /// <param name="sheetName">工作薄名</param> /// <param name="ds">数据源</param> public static string ExecuteExcelExport(string templatePath, string saveFolder, string sheetName, DataSet ds) { try { HSSFWorkbook hssfworkbook = InitializeWorkbook(templatePath); if (hssfworkbook == null) { return(null); } ISheet sheet1 = hssfworkbook.GetSheet(sheetName); //删除其他工作薄 int sheetIndex = hssfworkbook.GetSheetIndex(sheet1); if (hssfworkbook.NumberOfSheets > 1) { for (int i = hssfworkbook.NumberOfSheets - 1; i >= 0; i--) { if (i != sheetIndex) { hssfworkbook.RemoveSheetAt(i); } } } hssfworkbook.FirstVisibleTab = 0; //相关参数 int columnHeaderIndex = GetGridRowIndex(sheet1, "行号"); if (columnHeaderIndex <= 0) { columnHeaderIndex = GetGridRowIndex(sheet1, "序号"); } int gridRowCount = ds.Tables["Detail"].Rows.Count; DataTable dtEnterprise = ds.Tables["Enterprise"]; DataTable dtMain = ds.Tables["Main"]; DataTable dtDetail = ds.Tables["Detail"]; //重新构造工作薄 CreateSheet(sheet1, columnHeaderIndex + 2, gridRowCount); decimal totalMoney = 0; //总金额 decimal totalVolume = 0; //总体积 int totalBoxAmount = 0; //总箱数 int totalAmount = 0; //总数量 //企业信息 SetCellValueByTemplateStr(sheet1, "{$EnterpriseName}", dtEnterprise.Rows[0]["EnterpriseName"]); SetCellValueByTemplateStr(sheet1, "{$EnterprisePhone}", dtEnterprise.Rows[0]["Telephone"]); SetCellValueByTemplateStr(sheet1, "{$EnterpriseFax}", dtEnterprise.Rows[0]["Fax"]); SetCellValueByTemplateStr(sheet1, "{$EnterpriseMobile}", dtEnterprise.Rows[0]["Mobile"]); //数据 for (int i = 0; i < dtMain.Columns.Count; i++) { SetCellValueByTemplateStr(sheet1, "{$" + dtMain.Columns[i].ColumnName + "}", dtMain.Rows[0][i]); } //表明细 for (int i = 0; i < dtDetail.Rows.Count; i++) { for (int j = 0; j < dtDetail.Columns.Count; j++) { SetCellValueByTemplateStr(sheet1, "{$" + dtDetail.Columns[j].ColumnName + "}", dtDetail.Rows[i][j], columnHeaderIndex + 1, gridRowCount, true); } totalMoney += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["Money"]); totalBoxAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["BoxAmount"]); totalAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["Amount"]); totalVolume += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["OutVolumeDisplay"]); } //合计 SetCellValueByTemplateStr(sheet1, "{$TotalMoneyCH}", ConvertChIAmountHelper.ConvertSum(totalMoney + "")); //大写 SetCellValueByTemplateStr(sheet1, "{$TotalBoxAmount}", "共:" + totalBoxAmount + "箱"); //总箱数 SetCellValueByTemplateStr(sheet1, "{$TotalAmount}", totalAmount); //总数量 SetCellValueByTemplateStr(sheet1, "{$TotalMoney}", totalMoney); //总金额 SetCellValueByTemplateStr(sheet1, "{$TotalVolume}", totalVolume.ToString("f2")); //总体积 //生成文件 if (!System.IO.Directory.Exists(saveFolder)) { System.IO.Directory.CreateDirectory(saveFolder); } string savePath = saveFolder + dtMain.Rows[0]["OrderCode"] + ".xls"; using (FileStream file = new FileStream(savePath, FileMode.Create)) { hssfworkbook.Write(file); file.Close(); } return(dtMain.Rows[0]["OrderCode"] + ".xls"); } catch (Exception e) { } return(""); }
/// <summary> /// 根据收货出账单号生成Execl /// </summary> /// <param name="bill"></param> /// <param name="list"></param> public static void ExeclCreate(ReceivingBill bill, List <BillModel> list) { Log.Info("LMS开始生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new { ShippingMethodName = g.Key, TotalFee = g.Sum(p => p.Freight + p.FuelCharge + p.Register + p.Surcharge + p.TariffPrepayFee + p.SpecialFee + p.RemoteAreaFee) }).ToList(); using ( var file = new FileStream( AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open, FileAccess.Read)) { IWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheetNew = hssfworkbook.CloneSheet(0); sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称:{0}".FormatWith(bill.CustomerName)); sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间:{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd"))); IRow sourceRow = sheetNew.GetRow(2); int startRow = 2; foreach (var t in groupShipping) { startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow row = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref row); row.GetCell(0).SetCellValue(t.ShippingMethodName); row.GetCell(1).SetCellValue("总费用:{0}".FormatWith(t.TotalFee.ToString("F2"))); } startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow srow = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref srow); srow.GetCell(0).SetCellValue("结算人:{0}".FormatWith(bill.ReceivingBillAuditor)); startRow = startRow + 3; int startR = startRow; foreach (var b in list) { IRow row = sheetNew.CreateRow(startRow); row.CreateCell(0).SetCellValue(b.WayBillNumber); row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? ""); row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(4).SetCellValue(b.TrackingNumber ?? ""); row.CreateCell(5).SetCellValue(b.ChineseName); row.CreateCell(6).SetCellValue(b.InShippingMethodName); row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4")); row.CreateCell(8).SetCellValue(b.Weight.ToString("F4")); row.CreateCell(9).SetCellValue(b.CountNumber); row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2"))); row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2"))); row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2"))); row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2"))); row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2"))); row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2"))); row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2"))); startRow++; row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")"); } IRow totalRow = sheetNew.CreateRow(startRow); for (int i = 0; i < 18; i++) { totalRow.CreateCell(i).SetCellValue(""); } startR++; totalRow.Cells[9].SetCellValue("各项计费小计:"); totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")"); totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")"); totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")"); totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")"); totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")"); totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")"); totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")"); totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")"); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls"))) { File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls")); } var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create); hssfworkbook.RemoveSheetAt(0); hssfworkbook.Write(savefile); file.Close(); } Log.Info("LMS完成生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); }
/// <summary> /// Datable导出成Excel /// </summary> /// <param name="file">导出路径(包括文件名与扩展名)</param> /// <param name="dts"></param> /// <param name="onSheetBeforeAction"></param> /// <param name="onSheetAction"></param> public static void ExportDataTableToExcel(string file, List <DataTable> dts, Func <DataTable, DataTable> onSheetBeforeAction = null, Func <ISheet, DataTable, DataTable> onSheetAction = null) { IWorkbook workbook = null; string fileExt = Path.GetExtension(file).ToLower(); switch (fileExt) { case ".xlsx": case ".xlsm": workbook = new XSSFWorkbook(); break; case ".xls": workbook = new HSSFWorkbook(); break; default: throw new Exception("保存的文件格式不符合要求 !"); } foreach (DataTable dt in dts) { if (fileExt == ".xls" && dt.Rows.Count > 65536) { throw new Exception("数据量过大,请保存为.xlsx"); } if (onSheetBeforeAction != null) { if (onSheetBeforeAction.Invoke(dt) == null) { continue; } } ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); var value = dt.Columns[i].ColumnName; cell.SetCellValue(value); } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); var value = dt.Rows[i][j].ToString(); cell.SetCellValue(value); } } //默认一个样式以及自动宽度 for (int i = 0; i < dt.Columns.Count; i++) { var style = workbook.CreateCellStyle(); sheet.SetDefaultColumnStyle(i, style); sheet.AutoSizeColumn(i); } if (onSheetAction != null) { if (onSheetAction.Invoke(sheet, dt) == null) { workbook.RemoveSheetAt(workbook.GetSheetIndex(sheet)); } } //string regex = "(([\u4E00-\u9FA5])|([\u30A0-\u30FF])"; //var list = ConvertDataTableToRowsList(dt).Select(p => string.Join("|", p.Cast<string>().ToArray())).Select(p => Regex.IsMatch(p, regex)).ToList(); //for (int i = 0; i < list.Count; i++) //{ // var show = list[i]; // sheet.SetColumnHidden(i, !show); // //if (show) // // sheet.SetColumnWidth(0, 200*256 + 200); //} } }
private Ptg ParseIdentifier(String name) { if (look == ':' || look == '.') { // this Is a AreaReference GetChar(); while (look == '.') { // formulas can have . or .. or ... instead of : GetChar(); } String first = name; String second = Name; return(new AreaPtg(first + ":" + second)); } if (look == '!') { Match('!'); String sheetName = name; String first = Name; short externIdx = book.GetExternalSheetIndex(book.GetSheetIndex(sheetName)); if (look == ':') { Match(':'); String second = Name; if (look == '!') { //The sheet name was included in both of the areas. Only really //need it once Match('!'); String third = Name; if (!sheetName.Equals(second)) { throw new Exception("Unhandled double sheet reference."); } return(new Area3DPtg(first + ":" + third, externIdx)); } return(new Area3DPtg(first + ":" + second, externIdx)); } return(new Ref3DPtg(first, externIdx)); } if (name.Equals("TRUE", StringComparison.InvariantCultureIgnoreCase) || name.Equals("FALSE", StringComparison.InvariantCultureIgnoreCase)) { return(new BoolPtg(name.ToUpper())); } // This can be either a cell ref or a named range // Try To spot which it Is Regex regex = new Regex(CELL_REFERENCE_PATTERN); bool cellRef = regex.IsMatch(name); if (cellRef) { return(new RefPtg(name)); } for (int i = 0; i < book.NumberOfNames; i++) { // named range name matching Is case insensitive if (book.GetNameAt(i).NameName.ToLower().Equals(name.ToLower())) { return(new NamePtg(name, book)); } } throw new FormulaParseException("Found reference To named range \"" + name + "\", but that named range wasn't defined!"); }
/// <summary> /// 设置打印区域 /// </summary> /// <param name="firstRowIndex"></param> /// <param name="lastRowIndex"></param> /// <param name="firstColumnIndex"></param> /// <param name="lastColumnIndex"></param> public void SetPrintArea(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex) { this._workbook.SetPrintArea(_workbook.GetSheetIndex(_currentSheet), firstColumnIndex, lastColumnIndex, firstRowIndex, lastRowIndex); }
/** * @return human Readable string for debug purposes */ public String FormatAsString() { return("R=" + _srcRowNum + " C=" + _srcColNum + " ShIx=" + _workbook.GetSheetIndex(_sheet)); }
public int GetExternalSheetIndex(String sheetName) { int sheetIndex = _uBook.GetSheetIndex(sheetName); return(_iBook.CheckExternSheet(sheetIndex)); }
/// <summary> /// 带封面模板导出excel报告 /// </summary> /// <param name="dtTask"></param> /// <param name="mediaType"></param> /// <param name="adProductName"></param> /// <param name="planBegin"></param> /// <param name="planEnd"></param> /// <returns></returns> private MemoryStream CreatWebReportForFrame(DataTable dtTask, string mediaType, string adProductName, string scheduleName, DateTime planBegin, DateTime planEnd) { FileStream fileStream = null; string configPath = HttpContext.Current.Server.MapPath("~/xlsTemplate/"); fileStream = new FileStream(configPath + "ReportFrame.xls", FileMode.Open); hssfworkbook = new HSSFWorkbook(fileStream); int total = monitor.GetTaskPlanListCount(GetCondition("export")); if (hssfworkbook != null) { #region 处理封面 sheet = hssfworkbook.GetSheetAt(0); row = sheet.GetRow(14); cell = row.GetCell(0); cell.SetCellValue(adProductName + planBegin.ToString("yyyy年M月") + mediaType + "监测"); row = sheet.GetRow(40); cell = row.GetCell(0); cell.SetCellValue(planBegin.ToString("yyyy年M月d日") + "至" + planEnd.ToString("yyyy年M月d日")); #endregion #region 处理内容 ICellStyle normalStyle = NormalStyle; ICellStyle redStyle = RedStyle; ICellStyle cityStyle = CityStyle; ICellStyle blockStyle = BlockStyle; sheet = hssfworkbook.GetSheetAt(1); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), (string.IsNullOrEmpty(scheduleName) ? adProductName : scheduleName)); row = sheet.GetRow(4); cell = row.GetCell(0); cell.SetCellValue("排期名称:" + adProductName); cell = row.GetCell(2); cell.SetCellValue("投放时间:" + ConvertHelper.GetShortDateString(planBegin) + "至" + ConvertHelper.GetShortDateString(planEnd)); row = sheet.GetRow(5); cell = row.GetCell(0); cell.SetCellValue("全部投放量:" + total); cell = row.GetCell(1); cell.SetCellValue("抽检量:" + dtTask.Select(" Status=3").Length); cell = row.GetCell(2); cell.SetCellValue("抽检率:" + Math.Round((Convert.ToDouble(dtTask.Select(" Status=3").Length) / Convert.ToDouble(total) * 100), 2) + "%"); row = sheet.GetRow(6); cell = row.GetCell(0); cell.SetCellValue("抽检量:" + dtTask.Select(" Status=3").Length); cell = row.GetCell(1); cell.SetCellValue("合格量:" + dtTask.Select(" Status=3 AND AbnormalType in (0,5)").Length); cell = row.GetCell(2); cell.SetCellValue("不合格量:" + dtTask.Select(" Status=3 AND AbnormalType in (1,2,3,4,6,7)").Length); cell = row.GetCell(3); cell.SetCellValue("合格率:" + Math.Round((Convert.ToDouble(dtTask.Select(" Status=3 AND AbnormalType in (0,5)").Length) / Convert.ToDouble(dtTask.Select(" Status=3").Length) * 100), 2) + "%"); row = sheet.GetRow(7); cell = row.GetCell(0); cell.SetCellValue("异常原因:"); cell = row.GetCell(1); cell.SetCellValue("未上画:" + dtTask.Select(" Status=3 AND AbnormalType=3").Length); cell = row.GetCell(2); cell.SetCellValue("遮挡:" + dtTask.Select(" Status=3 AND AbnormalType=6").Length); cell = row.GetCell(3); cell.SetCellValue("破损:" + dtTask.Select(" Status=3 AND AbnormalType=4").Length); row = sheet.CreateRow(10); cell = row.CreateCell(0); cell.CellStyle = TitleStyle; cell.SetCellValue("城市"); cell = row.CreateCell(1); cell.CellStyle = TitleStyle; cell.SetCellValue("楼宇名称"); cell.CellStyle = TitleStyle; cell = row.CreateCell(2); cell.CellStyle = TitleStyle; cell.SetCellValue("未上画"); cell = row.CreateCell(3); cell.CellStyle = TitleStyle; cell.SetCellValue("遮挡"); cell = row.CreateCell(4); cell.CellStyle = TitleStyle; cell.SetCellValue("破损"); cell = row.CreateCell(5); cell.CellStyle = TitleStyle; cell.SetCellValue("正常"); cell = row.CreateCell(6); cell.CellStyle = TitleStyle; cell.SetCellValue("合计"); int notPaintTotal = 0, hiddenTotal = 0, normalTotal = 0, breakTotal = 0; int tempNum = 11; DataTable dt = monitor.GetReportDetailList(GetCondition(), 1, 9999).Tables[0]; DataTable dtCity = monitor.GetCityReportDetailList(GetCondition()).Tables[0]; DataTable dtNew = new DataTable(); dtNew.Columns.Add(new DataColumn("RegionId", typeof(string))); dtNew.Columns.Add(new DataColumn("City", typeof(string))); dtNew.Columns.Add(new DataColumn("RegionName", typeof(string))); dtNew.Columns.Add(new DataColumn("BlockName", typeof(string))); dtNew.Columns.Add(new DataColumn("NotPaintCount", typeof(int))); dtNew.Columns.Add(new DataColumn("HiddenCount", typeof(int))); dtNew.Columns.Add(new DataColumn("BreakCount", typeof(int))); dtNew.Columns.Add(new DataColumn("NormalCount", typeof(int))); for (int i = 0; i < dtCity.Rows.Count; i++) { DataRow dr = dtNew.NewRow(); dr["RegionId"] = dtCity.Rows[i]["RegionId"]; dr["City"] = dtCity.Rows[i]["RegionName"]; dr["RegionName"] = dtCity.Rows[i]["RegionName"] + " Total"; dr["BlockName"] = ""; dr["NotPaintCount"] = dtCity.Rows[i]["NotPaintCount"]; dr["HiddenCount"] = dtCity.Rows[i]["HiddenCount"]; dr["BreakCount"] = dtCity.Rows[i]["BreakCount"]; dr["NormalCount"] = dtCity.Rows[i]["NormalCount"]; dtNew.Rows.Add(dr); DataRow[] drc = dt.Select(" RegionId='" + dtCity.Rows[i]["RegionId"] + "'"); for (int j = 0; j < drc.Length; j++) { dr = dtNew.NewRow(); dr["RegionId"] = drc[j]["RegionId"]; dr["City"] = drc[j]["RegionName"]; dr["RegionName"] = j == 0 ? drc[j]["RegionName"] : ""; dr["BlockName"] = drc[j]["BlockName"]; dr["NotPaintCount"] = drc[j]["NotPaintCount"]; dr["HiddenCount"] = drc[j]["HiddenCount"]; dr["BreakCount"] = drc[j]["BreakCount"]; dr["NormalCount"] = drc[j]["NormalCount"]; dtNew.Rows.Add(dr); } notPaintTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["NotPaintCount"]); hiddenTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["HiddenCount"]); normalTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["NormalCount"]); breakTotal += ConvertHelper.GetInteger(dtCity.Rows[i]["BreakCount"]); } for (int j = 0; j < dtNew.Rows.Count; j++) { bool IsCityTr = dtNew.Rows[j]["RegionName"].ToString().Contains("Total"); row = sheet.CreateRow(tempNum); cell = row.CreateCell(0); cell.CellStyle = IsCityTr ? cityStyle : blockStyle; cell.SetCellValue(dtNew.Rows[j]["RegionName"].ToString()); cell = row.CreateCell(1); cell.CellStyle = IsCityTr ? cityStyle : blockStyle; cell.CellStyle.ShrinkToFit = true; cell.SetCellValue(dtNew.Rows[j]["BlockName"].ToString()); cell = row.CreateCell(2); cell.CellStyle = dtNew.Rows[j]["NotPaintCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle); cell.SetCellValue(dtNew.Rows[j]["NotPaintCount"].ToString() == "0" ? "" : dtNew.Rows[j]["NotPaintCount"].ToString()); cell = row.CreateCell(3); cell.CellStyle = dtNew.Rows[j]["HiddenCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle); cell.SetCellValue(dtNew.Rows[j]["HiddenCount"].ToString() == "0" ? "" : dtNew.Rows[j]["HiddenCount"].ToString()); cell = row.CreateCell(4); cell.CellStyle = dtNew.Rows[j]["BreakCount"].ToString() == "0" ? (IsCityTr ? cityStyle : blockStyle) : (IsCityTr ? cityStyle : AbnormalStyle); cell.SetCellValue(dtNew.Rows[j]["BreakCount"].ToString() == "0" ? "" : dtNew.Rows[j]["BreakCount"].ToString()); cell = row.CreateCell(5); cell.CellStyle = IsCityTr ? cityStyle : blockStyle; cell.SetCellValue(dtNew.Rows[j]["NormalCount"].ToString() == "0" ? "" : dtNew.Rows[j]["NormalCount"].ToString()); cell = row.CreateCell(6); cell.CellStyle = IsCityTr ? cityStyle : blockStyle; cell.SetCellValue((ConvertHelper.GetInteger(dtNew.Rows[j]["NotPaintCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["HiddenCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["BreakCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["NormalCount"])).ToString() == "0" ? "" : (ConvertHelper.GetInteger(dtNew.Rows[j]["NotPaintCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["HiddenCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["BreakCount"]) + ConvertHelper.GetInteger(dtNew.Rows[j]["NormalCount"])).ToString()); tempNum++; } row = sheet.CreateRow(tempNum); cell = row.CreateCell(0); cell.CellStyle = blockStyle; cell.SetCellValue("合计"); cell = row.CreateCell(1); cell.CellStyle = blockStyle; cell.SetCellValue(""); cell = row.CreateCell(2); cell.CellStyle = blockStyle; cell.SetCellValue(notPaintTotal); cell = row.CreateCell(3); cell.CellStyle = blockStyle; cell.SetCellValue(hiddenTotal); cell = row.CreateCell(4); cell.CellStyle = blockStyle; cell.SetCellValue(breakTotal); cell = row.CreateCell(5); cell.CellStyle = blockStyle; cell.SetCellValue(normalTotal); cell = row.CreateCell(6); cell.CellStyle = blockStyle; cell.SetCellValue(notPaintTotal + hiddenTotal + breakTotal + normalTotal); sheet = hssfworkbook.GetSheetAt(2); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), "监测明细"); int rowNum = 1; for (int i = 0; i < dtTask.Rows.Count; i++) { row = sheet.CreateRow(rowNum); cell = row.CreateCell(0); cell.CellStyle = normalStyle; cell.SetCellValue(i + 1);//序号 cell = row.CreateCell(1); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["RegionName"])); cell = row.CreateCell(2); cell.CellStyle = normalStyle; cell.CellStyle.ShrinkToFit = true; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["StreetAddress"])); cell = row.CreateCell(3); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["BlockName"])); cell = row.CreateCell(4); cell.CellStyle = normalStyle; cell.CellStyle.ShrinkToFit = true; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["PointName"])); cell = row.CreateCell(5); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["MediaType"])); cell = row.CreateCell(6); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["AdProductName"])); cell = row.CreateCell(7); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetDateTimeString(dtTask.Rows[i]["BeginDate"], "yyyy.MM.dd") + "-" + ConvertHelper.GetDateTimeString(dtTask.Rows[i]["EndDate"], "yyyy.MM.dd")); cell = row.CreateCell(8); cell.CellStyle = (ConvertHelper.GetInteger(dtTask.Rows[i]["AbnormalType"]) == 0 || ConvertHelper.GetInteger(dtTask.Rows[i]["AbnormalType"]) == 5 ? normalStyle : redStyle); cell.SetCellValue(GetTaskPlanStatus(ConvertHelper.GetString(dtTask.Rows[i]["Status"]), ConvertHelper.GetString(dtTask.Rows[i]["AbnormalType"]), "export")); cell = row.CreateCell(9); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["AuditReason"])); cell = row.CreateCell(10); cell.CellStyle = normalStyle; cell.SetCellValue(ConvertHelper.GetString(dtTask.Rows[i]["SpareOne"])); rowNum++; } } #endregion if (fileStream != null) { fileStream.Close(); } MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); return(ms); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <param name="startIndex">读取标头开始行,下标0开始</param> /// <param name="sheetList">sheet集合,如sheet1,sheet2,sheet3</param> /// <param name="errorMessage">返回的异常数据</param> /// <returns></returns> public static List <DataTable> GetListByImport(string strFileName, int startIndex, List <string> sheetName, ref string errorMessage) { List <DataTable> tableList = new List <DataTable>(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } for (int i = 0; i < sheetName.Count; i++) { try { if (hssfworkbook.GetSheetIndex(sheetName[i]) != -1) { } else { errorMessage = "上传的EXCEL不规范!"; return(null); } } catch { errorMessage = sheetName[i] + "不存在,请不要随意修改EXCEL!"; return(null); } } for (int sheetIndex = 0; sheetIndex < hssfworkbook.NumberOfSheets; sheetIndex++) { ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex); DataTable dt = new DataTable(hssfworkbook.GetSheetName(sheetIndex)); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(startIndex); int cellCount = 0; try { cellCount = headerRow.LastCellNum; } catch { tableList.Add(dt); continue; } for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); try { dt.Columns.Add(cell.ToString()); } catch { cellCount = j; break; } } for (int i = (sheet.FirstRowNum + 1 + startIndex); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); try { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).ToString().Trim().Replace(" ", "").Length == 0) { dataRow[j] = ""; //break; //return dt; } else { dataRow[j] = row.GetCell(j).ToString().Trim().Replace(" ", ""); } } //else //{ // return dt; //} } } catch { break; } dt.Rows.Add(dataRow); } tableList.Add(dt); } return(tableList); }
public static MemoryStream ToMemoryStram(SummaryInfo summaryInfo, List <Matrix <InspectionDataReport> > list_InspectionDataReportMatrix, List <DefectTypeInfoReport> list_DefectTypeInfoReport, DefectStatistics defectStatistics, int ReportModel) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(ExcelText.SheetName_SummaryOperator); #region SummaryOperator int rowIndex = 0; int code2drowIndex = 0; //1、产品编号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode); //2、批次号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot); //3、设备号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine); //4、操作员 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator); //5、开始日期 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartDate, summaryInfo.StartDate); //6、开始时间 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartTime, summaryInfo.StartTime); //7、结束日期 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndDate, summaryInfo.EndDate); //8、结束时间 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndTime, summaryInfo.EndTime); rowIndex++; //10、批次理论盘数 没有输入默认10 已屏蔽 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.TotalNumberOfStrips, summaryInfo.TotalNumberOfStrips.ToString()); //11、已检测的盘数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsInspected, summaryInfo.NumberOfStripsInspected.ToString()); //12、未检测的盘数 已屏蔽 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsNotInspected, summaryInfo.NumberOfStripsNotInspected.ToString()); //13、单盘芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.QuantityOfDevicesPerStrip, summaryInfo.QuantityOfDevicesPerStrip.ToString()); //14、批次理论芯片数 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStartQuantity, summaryInfo.NumberOfStartQuantity.ToString()); //15、批次已检测芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesInspected, summaryInfo.NumberOfDevicesInspected.ToString()); //16、每小时检测芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DevicesPerHour, string.Format("{0:0.##}", summaryInfo.DevicesPerHour)); rowIndex++; //17、合格芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesPassed, summaryInfo.NumberOfDevicesPassed.ToString()); //18、不合格的芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesRejected, summaryInfo.NumberOfDevicesRejected.ToString()); //19、误检芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesFalseCalled, summaryInfo.NumberOfDevicesFalseCalled.ToString()); //新增 OK复看为NG数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, "OK复看NG数", summaryInfo.NumberOfReviewNG.ToString()); //20、跳过的芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfNoDies, summaryInfo.NumberOfNoDies.ToString()); //21、合格率 存在二维码时根据二维码总数 if (ReportModel == 1) { //二维码总数 只在武汉二维码报表模式生成 CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}", "二维码总数"), summaryInfo.CodeNumber.ToString()); double temp = summaryInfo.NumberOfDevicesPassed / (double)summaryInfo.CodeNumber; CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", temp.ToString())); } else { CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", summaryInfo.YieldByDevice)); } //22、误检率 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.FalseCallDevicePercent, string.Format("{0:0.##}", summaryInfo.FalseCallDevicePercent)); //新增 当存在 K2N时显示算上复看OK到KG的误检率 if (summaryInfo.NumberOfReviewNG != 0) { // CreateOneRowTwoColumnCells(sheet, ref rowIndex, "误检率(%包含复看不合格)", string.Format("{0:0.##}", summaryInfo.DevicePercentOfK2N)); } rowIndex++; //23、错误种类出现数量 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType, ExcelText.Count); foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport) { if (defectTypeInfoReport.Count > 0) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", defectTypeInfoReport.Index, defectTypeInfoReport.DefectType), defectTypeInfoReport.Count.ToString()); } } rowIndex++; //24、错误种类根据错误优先级统计数量 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType + "(根据错误优先级表)", ExcelText.Count); if (defectStatistics.Flag == false) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告,存在未规定的错误优先级", ""); } else { foreach (KeyValuePair <int, int> ds in defectStatistics.CountDefectResult) { if (defectStatistics.CountN2KDefectResult.ContainsKey(ds.Key)) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType), string.Format("{0}-{1}={2}", ds.Value, defectStatistics.CountN2KDefectResult[ds.Key], ds.Value - defectStatistics.CountN2KDefectResult[ds.Key])); } else { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType), ds.Value.ToString()); } } //25、是否存在优先级相同并且一同输出的 if (defectStatistics.List_RepeatPriority.Count != 0) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告 存在优先级相同情况", ""); for (int i = 0; i < defectStatistics.List_RepeatPriority.Count; i++) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectStatistics.List_RepeatPriority[i].ToString(), ""); } } } sheet.SetColumnWidth(0, 80 * 256); sheet.SetColumnWidth(0, 30 * 256); #endregion workbook.CreateSheet(ExcelText.SheetName_MapOperator); workbook.CreateSheet(ExcelText.SheetName_UDD); int T = 0; #region 创建错误图片Sheet sheet = workbook.CreateSheet("错误图片"); int sheetIndex = workbook.GetSheetIndex(sheet); sheet.SetColumnWidth(1, 30 * 256); sheet.SetColumnWidth(2, 30 * 256); int ImageSheetNum = 0; T = T + rowIndex; rowIndex = 0; foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix) { if (rowIndex >= 65000) { ImageSheetNum++; sheet = workbook.CreateSheet("错误图片续_" + ImageSheetNum); sheetIndex = workbook.GetSheetIndex(sheet); rowIndex = 0; } HSSFPatriarch sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFCell cell = null; int defectCountInFrame = 0; //int sheetContinuedIndexEachStrip = 0; int ImageCol = 0; foreach (InspectionDataReport dataReport in inspectionDataReportMatrix) { defectCountInFrame++; int TempImage_row = 0; ImageCol = 5; int startRow = 0; if (dataReport == null) { continue; } if (dataReport.List_DefectData.Count == 0) { if (dataReport.InspectionResult != InspectionResult.K2N) { continue; } } startRow = rowIndex; sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0); cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1)); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); cell.SetCellValue(string.Format("第{0}条", dataReport.FrameIndex)); if (dataReport.Code2D != "null") { cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(2); cell.SetCellValue(string.Format("二维码:{0}", dataReport.Code2D)); } // 创建图片 所有缺陷在一张图中 如果存在拍多次的情况 进行横排 第一张图 col为5 System.Drawing.Image[] images = new System.Drawing.Image[dataReport.List_GeneralImageTempPath.Count]; for (int i = 0; i < images.Count(); i++) { //创建图片单元格 cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(ImageCol); if (File.Exists(dataReport.List_GeneralImageTempPath[i])) { byte[] bytes = File.ReadAllBytes(dataReport.List_GeneralImageTempPath[i]); int index = workbook.AddPicture(bytes, PictureType.JPEG); System.Drawing.Image image; using (MemoryStream ms = new MemoryStream(bytes)) { image = System.Drawing.Image.FromStream(ms); } double imageWidth = image.Width; double imageHeight = image.Height; image.Dispose(); double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; int imageInCellColumns = (int)(imageWidth / cellWidth); int imageInCellRows = (int)(imageHeight / cellHeight); double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, ImageCol, rowIndex, imageInCellColumns + ImageCol, rowIndex + imageInCellRows); sheetPatriarch.CreatePicture(anchor, index); //计算图片高占多少个单元格 TempImage_row = (int)Math.Ceiling(imageHeight / cellHeight) + 1; NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex + TempImage_row, 1); dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString()); } else { cell.SetCellValue("图片不存在"); } ImageCol = ImageCol + 10; } rowIndex = rowIndex + 1; //记录错误信息 foreach (var defectReort in dataReport.List_DefectData) { rowIndex = rowIndex + 1; sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); if (defectReort.DefectTypeIndex <= 0 || defectReort.DefectTypeIndex > list_DefectTypeInfoReport.Count) { cell.SetCellValue(string.Format("{0}:{1}", defectReort.DefectTypeIndex, defectReort.ErrorDetail)); } else { cell.SetCellValue(string.Format("{0}:{1}", list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType, defectReort.ErrorDetail)); } } rowIndex = rowIndex + 1; string[] show_defecttype = new string[dataReport.List_DefectData.Count]; for (int i = 0; i < dataReport.List_DefectData.Count; i++) { show_defecttype[i] = dataReport.List_DefectData[i].DefectTypeIndex.ToString(); } sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); cell.SetCellValue(string.Format("详细错误码:{0}", string.Join(";", show_defecttype))); //判断 图片所占行数多 还是写入的错误信息行数多 if (rowIndex - startRow <= TempImage_row) { rowIndex = startRow + TempImage_row + 2; } #region 每个缺陷对应一张图 已屏蔽 //foreach (var defectReort in dataReport.List_DefectData) //{ // if (rowIndex>= 32757) // { // sheetContinuedIndexEachStrip++; // sheet = workbook.CreateSheet(inspectionDataReportMatrix.FrameName + " " + ExcelText.Continued + sheetContinuedIndexEachStrip.ToString()); // sheetIndex = workbook.GetSheetIndex(sheet); // sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); // rowIndex = 0; // } // defectCountInFrame++; // sheet.CreateRow(rowIndex); // sheet.CreateRow(rowIndex + 1); // HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0); // cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1)); // cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); // cell.SetCellValue(list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType); // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(1); // cell.SetCellValue(defectReort.ErrorDetail); // rowIndex += 2; // sheet.CreateRow(rowIndex); // cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); // if (File.Exists(defectReort.ImageTempPath)) // { // byte[] bytes = File.ReadAllBytes(defectReort.ImageTempPath); // int index = workbook.AddPicture(bytes, PictureType.JPEG); // System.Drawing.Image image; // using (MemoryStream ms = new MemoryStream(bytes)) // { // image = System.Drawing.Image.FromStream(ms); // } // double imageWidth = image.Width; // double imageHeight = image.Height; // image.Dispose(); // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // int imageInCellColumns = (int)(imageWidth / cellWidth); // int imageInCellRows = (int)(imageHeight / cellHeight); // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; // // //HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // //commentAnchor.AnchorType = AnchorType.MoveDontResize; // //HSSFComment comment = (HSSFComment)sheetPatriarch.CreateCellComment(commentAnchor); // //comment.SetBackgroundImage(index); // //cell.CellComment = (comment); // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 1, rowIndex, imageInCellColumns + 1, rowIndex + imageInCellRows); // sheetPatriarch.CreatePicture(anchor, index); // if (!hasRecordLink) // { // NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex, 1); // dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString()); // hasRecordLink = true; // } // rowIndex += (int)Math.Ceiling(imageHeight / cellHeight) + 1; // } // else // { // cell.SetCellValue("图片不存在"); // } //} #endregion } if (defectCountInFrame == 0) { workbook.RemoveSheetAt(sheetIndex); } } #endregion #region MapOperator sheet = workbook.GetSheet(ExcelText.SheetName_MapOperator); if (ReportModel != 1) { sheet.DefaultColumnWidth = 1; } //sheet = workbook.GetSheetAt(1); rowIndex = 0; //1、产品编号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode); //2、批次号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot); //3、设备号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine); //4、操作员 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator); rowIndex++; //5、检测结果样例 Dictionary <InspectionResult, ICellStyle> dict_result_style = new Dictionary <InspectionResult, ICellStyle>(); ICellStyle exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Green; dict_result_style.Add(InspectionResult.OK, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.OK), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Red; dict_result_style.Add(InspectionResult.NG, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.NG), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Yellow; dict_result_style.Add(InspectionResult.N2K, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.N2K), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Orange; dict_result_style.Add(InspectionResult.K2N, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.K2N), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.SkyBlue; dict_result_style.Add(InspectionResult.SKIP, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.SKIP), exampleStyle); rowIndex++; //6、分盘绘制图谱 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); ICellStyle centerAlignmentStyle = workbook.CreateCellStyle(); centerAlignmentStyle.Alignment = HorizontalAlignment.Center; int sheetContinuedIndex = 0; foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix) { if (inspectionDataReportMatrix.ColumnCount < 254) { //创建单元格 for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++) { IRow createRow = sheet.CreateRow(rowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++) { createRow.CreateCell(j); } } //赋值条号 sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) { ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2); cell.SetCellValue((i + 1).ToString()); cell.CellStyle = centerAlignmentStyle; } //创建二维码列表 if (ReportModel == 1) { code2drowIndex = rowIndex + inspectionDataReportMatrix.RowCount + 2; //创建单元格 for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++) { IRow createRow = sheet.CreateRow(code2drowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++) { createRow.CreateCell(j); } } //赋值二维码 sheet.GetRow(code2drowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", "二维码", inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { sheet.GetRow(code2drowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } //for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) //{ // ICell cell = sheet.GetRow(code2drowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2); // //cell.SetCellValue((i + 1).ToString()); // cell.CellStyle = centerAlignmentStyle; //} foreach (InspectionDataReport data1 in inspectionDataReportMatrix) { if (data1 == null) { continue; } ICell cell; if (inspectionDataReportMatrix.ColumnCount < 254) { cell = sheet.GetRow(code2drowIndex + data1.RowIndex).GetCell(data1.ColumnIndex + 2); } else { cell = sheet.GetRow(code2drowIndex + data1.ColumnIndex).GetCell(data1.RowIndex + 2); } cell.SetCellValue(data1.Code2D); } } } else { if (rowIndex + inspectionDataReportMatrix.ColumnCount + 1 >= 32757) { sheetContinuedIndex++; sheet = workbook.CreateSheet(ExcelText.SheetName_MapOperator + " " + ExcelText.Continued + sheetContinuedIndex.ToString()); rowIndex = 0; } for (int i = 0; i < inspectionDataReportMatrix.ColumnCount + 1; i++) { IRow createRow = sheet.CreateRow(rowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.RowCount + 2; j++) { createRow.CreateCell(j); } } sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) { sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.ColumnCount).GetCell(i + 2); cell.SetCellValue((i + 1).ToString()); cell.CellStyle = centerAlignmentStyle; } } foreach (InspectionDataReport data in inspectionDataReportMatrix) { if (data == null) { continue; } ICell cell; if (inspectionDataReportMatrix.ColumnCount < 254) { cell = sheet.GetRow(rowIndex + data.RowIndex).GetCell(data.ColumnIndex + 2); } else { cell = sheet.GetRow(rowIndex + data.ColumnIndex).GetCell(data.RowIndex + 2); } cell.CellStyle = dict_result_style[data.InspectionResult]; if (data.InspectionResult == InspectionResult.NG) { //string[] defectTypes = new string[data.List_DefectData.Count]; //for (int i = 0; i < data.List_DefectData.Count; i++) //{ // defectTypes[i] = data.List_DefectData[i].DefectTypeIndex.ToString(); //} string[] noRepeatDefectTyoes = new string[data.Priority_DetectType.Count]; for (int i = 0; i < data.Priority_DetectType.Count; i++) { noRepeatDefectTyoes[i] = data.Priority_DetectType[i].ToString(); } //string[] noRepeatDefectTyoes = data.List_DefectData.Select(d => d.DefectTypeIndex.ToString()).Distinct().ToArray(); cell.SetCellValue(string.Join(";", noRepeatDefectTyoes)); } if (data.InspectionResult == InspectionResult.NG || data.InspectionResult == InspectionResult.N2K || data.InspectionResult == InspectionResult.K2N) { if (data.ExcelDefectImageLink == null) { continue; } HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document); link.Address = data.ExcelDefectImageLink; cell.Hyperlink = link; } #region 对NG图 以及 N2K图添加批注 鼠标悬停可以显示图片 已屏蔽 //if (data.InspectionResult == InspectionResults.NG || data.InspectionResult == InspectionResults.N2K) //{ // //int defectCount = data.List_DefectData.Count; // //System.Drawing.Image[] images = new System.Drawing.Image[defectCount]; // System.Drawing.Image[] images = new System.Drawing.Image[data.List_GeneralImageTempPath.Count]; // double imageMaxWidth = 0; // double imageTotalHeight = 0; // //for (int i = 0; i < defectCount; i++) // //{ // // if (!File.Exists(data.List_DefectData[i].ImageTempPath)) break; // // byte[] bytesOfImage = File.ReadAllBytes(data.List_DefectData[i].ImageTempPath); // // using (MemoryStream ms = new MemoryStream(bytesOfImage)) // // { // // images[i] = System.Drawing.Image.FromStream(ms); // // } // // if (images[i].Width > imageMaxWidth) // // { // // imageMaxWidth = images[i].Width; // // } // // imageTotalHeight += images[i].Height; // //} // for (int i = 0; i < images.Count(); i++) // { // if (!File.Exists(data.List_GeneralImageTempPath[i])) break; // byte[] bytesOfImage = File.ReadAllBytes(data.List_GeneralImageTempPath[i]); // using (MemoryStream ms = new MemoryStream(bytesOfImage)) // { // images[i] = System.Drawing.Image.FromStream(ms); // } // if (images[i].Width > imageMaxWidth) // { // imageMaxWidth = images[i].Width; // } // imageTotalHeight += images[i].Height; // } // if (imageMaxWidth == 0 || imageTotalHeight == 0) continue; // Bitmap concatImage = new Bitmap((int)imageMaxWidth, (int)imageTotalHeight); // concatImage.SetResolution(300, 300); // Graphics g = Graphics.FromImage(concatImage); // float drawStartHeight = 0; // for (int i = 0; i < images.Count(); i++) // { // g.DrawImage(images[i], 0, drawStartHeight); // drawStartHeight += images[i].Height; // images[i].Dispose(); // } // byte[] bytesOfConcatImage; // using (MemoryStream ms = new MemoryStream()) // { // concatImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); // bytesOfConcatImage = ms.ToArray(); // } // int index = workbook.AddPicture(bytesOfConcatImage, PictureType.JPEG); // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip) // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI // //要计算Excel的行高,就先把它行转换到英寸,再乘小DPI就可以得到像素 // //像素 = (Excel的行高度 / 72) * DPI // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // int imageInCellColumns = (int)(imageMaxWidth / cellWidth); // int imageInCellRows = (int)(imageTotalHeight / cellHeight); // double offsetX = (imageMaxWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // double offsetY = (imageTotalHeight - cellHeight * imageInCellRows) / cellHeight * 256; // IClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // commentAnchor.AnchorType = AnchorType.MoveDontResize; // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor); // comment.SetBackgroundImage(index); // cell.CellComment = comment; // HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document); // link.Address = data.ExcelDefectImageLink; // cell.Hyperlink = link; // //NPOI.SS.Util.CellReference cr = new NPOI.SS.Util.CellReference("A1"); // //ICellStyle hlink_style = hssfworkbook.CreateCellStyle(); // //IFont hlink_font = hssfworkbook.CreateFont(); // //hlink_font.Underline = FontUnderlineType.Single; // //hlink_font.Color = HSSFColor.Blue.Index; // //hlink_style.SetFont(hlink_font); // //cell.CellStyle = (hlink_style); // //HSSFCell picIndexCell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(picColumnIndex); // //picIndexCell.SetCellValue(string.Format("{0}-{1}", data.Row + 1, data.Column + 1)); // //picIndexCell.CellStyle = centerAlignmentStyle; // //if (!data.PicturePath.Equals(string.Empty)) // //{ // // if (File.Exists(data.PicturePath)) // // { // // byte[] bytes = File.ReadAllBytes(data.PicturePath); // // int index = workbook.AddPicture(bytes, PictureType.JPEG); // // System.Drawing.Image image; // // using (MemoryStream ms = new MemoryStream(bytes)) // // { // // image = System.Drawing.Image.FromStream(ms); // // } // // double imageWidth = image.Width; // // double imageHeight = image.Height; // // image.Dispose(); // // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip) // // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI // // //要计算Excel的行高,就先把它行转换到英寸,再乘小DPI就可以得到像素 // // //像素 = (Excel的行高度 / 72) * DPI // // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // // int imageInCellColumns = (int)(imageWidth / cellWidth); // // int imageInCellRows = (int)(imageHeight / cellHeight); // // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; // // HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // // commentAnchor.AnchorType = AnchorType.MoveDontResize; // // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor); // // comment.SetBackgroundImage(index); // // cell.CellComment = (comment); // // //HSSFClientAnchor anchor = new HSSFClientAnchor(100, 0, 923, 0, picColumnIndex, rowIndex + 1, picColumnIndex, rowIndex + dieDataSQLMatrix.RowCount); // // //patriarch.CreatePicture(anchor, index); // // } // //} // //else // //{ // // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(picColumnIndex); // // cell.SetCellValue("图片不存在"); // // cell.CellStyle = centerAlignmentStyle; // //} // //picColumnIndex++; //} #endregion } if (inspectionDataReportMatrix.ColumnCount < 254) { if (ReportModel == 1) { rowIndex = code2drowIndex; } rowIndex += inspectionDataReportMatrix.RowCount + 2; } else { rowIndex += inspectionDataReportMatrix.ColumnCount + 2; } } sheet.SetColumnWidth(0, 20 * 256); #endregion #region UDD sheet = workbook.GetSheet(ExcelText.SheetName_UDD); //sheet = workbook.GetSheetAt(2); rowIndex = 0; foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectTypeInfoReport.Index.ToString(), defectTypeInfoReport.DefectType); } #endregion using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); return(ms); } }