/// <summary> /// 合并单元格。坐标:(x1,x2,y1,y2) /// </summary> /// <param name="startRowIndex">起始行索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="startColumnIndex">开始列索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <returns></returns> public IExcel MergeCell(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) { var region = new NPOI.SS.Util.CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex); _sheet.AddMergedRegion(region); return(this); }
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet) { var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2); sheet.AddMergedRegion(merge); var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var caminho = $"{diretorioAtual}/Recursos/logoVLI.png"; byte[] data = ArquivosUtil.RetornarArquivo(caminho); int pictureIndex = workbook.AddPicture(data, PictureType.JPEG); ICreationHelper helper = workbook.GetCreationHelper(); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = helper.CreateClientAnchor(); anchor.Col1 = 1; anchor.Row1 = 1; IPicture picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/ return(sheet); }
/// <summary> /// 删除行,解决因为shiftRows上移删除行而造成的格式错乱问题 /// </summary> /// <param name="worksheet">目标sheet</param> /// <param name="startRow">需要删除开始行(从0开始)</param> /// <param name="count">删除行数</param> public static void DelRows(ISheet worksheet, int startRow, int count) { int NumMergedRegions = worksheet.NumMergedRegions; //int[,] allMergedRegion = getAllMergedRegions(worksheet); for (int i = NumMergedRegions - 1; i >= 0; i--) { NPOI.SS.Util.CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow >= startRow + count || cellRangeAddress.LastRow <= startRow) { //只有一行的合并单元格FirstRow==LastRow if (cellRangeAddress.FirstRow == cellRangeAddress.LastRow) { //刚好在删除区域的startRow或endRow if (cellRangeAddress.FirstRow == startRow || cellRangeAddress.FirstRow == startRow + count - 1) { worksheet.RemoveMergedRegion(i); } } } else { //该合并单元格的行已经在被删除的区域内,所以解除该合并单元格 //如果不删除该合并单元格将会导致全部格式错乱 worksheet.RemoveMergedRegion(i); } } worksheet.ShiftRows(startRow + count, worksheet.LastRowNum, -count, true, false); }
public static void TrainingReport(IList <SelectableDriver> drivers, string absoluteFileName) { IWorkbook workbook = GetWorkbook(absoluteFileName); ISheet mainWorksheet = CreateSheet(workbook, "Training Report"); // Get All The CellStyles ICellStyle titleCellStyle = GetTitleStyle(workbook); ICellStyle tableHeaderCellStyle = GetTableHeaderStyle(workbook); ICellStyle infoCellStyle = GetInfoStyle(workbook); ICellStyle centeredInfoCellStyle = GetCenteredInfoStyle(workbook); ICellStyle boldInfoCellStyle = GetBoldInfoFont(workbook); // Add Titles and Date in Report. IRow titleRow = mainWorksheet.CreateRow(0); var reportTitleCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3); mainWorksheet.AddMergedRegion(reportTitleCellRangeAddress); AddCell(titleRow, 0, "Training Report", titleCellStyle); AddCell(titleRow, 5, DateTime.Now.ToString(DateFormat), boldInfoCellStyle); // Add Headers. IRow headerRow = mainWorksheet.CreateRow(2); AddCell(headerRow, 0, "Last Name", tableHeaderCellStyle); AddCell(headerRow, 1, "First Name", tableHeaderCellStyle); AddCell(headerRow, 2, "Group", tableHeaderCellStyle); AddCell(headerRow, 3, "Lesson(s) Completed", tableHeaderCellStyle); AddCell(headerRow, 4, "Total Lessons", tableHeaderCellStyle); AddCell(headerRow, 5, "Course Completed?", tableHeaderCellStyle); // Add data now. int rowIndex = 3; // Adding data now. foreach (Driver driver in drivers) { int completedLessonCount = driver.GroupedAttemptsByLessons.Count(x => x.IsComplete); int totalLessonCount = driver.GroupedAttemptsByLessons.Count(); string couserCompleted = completedLessonCount == totalLessonCount ? "Yes" : "No"; IRow row = mainWorksheet.CreateRow(rowIndex); AddCell(row, 0, driver.LastName, infoCellStyle); AddCell(row, 1, driver.FirstName, infoCellStyle); AddCell(row, 2, driver.Group != null ? driver.Group.Name : string.Empty, infoCellStyle); AddCell(row, 3, completedLessonCount.ToString(), centeredInfoCellStyle); AddCell(row, 4, totalLessonCount.ToString(), centeredInfoCellStyle); AddCell(row, 5, couserCompleted, centeredInfoCellStyle); rowIndex++; } AutoSizeColumns(mainWorksheet, 6); // Creating file now using (FileStream fs = new FileStream(absoluteFileName, FileMode.Create)) { workbook.Write(fs); } workbook.Close(); }
/// <summary> /// 跨sheet复制多行,连合并单元格 /// </summary> /// <param name="worksheet">源sheet</param> /// <param name="targetsheet">目标</param> /// <param name="sourceRowNum">源行值</param> /// <param name="copyRowsCount">复制行数</param> /// <param name="destinationRowNum">目标行值</param> /// <param name="cover">是否以覆盖原来内容,true为覆盖,false为插入</param> public static void CopyRowsOverSheet(ISheet worksheet, ISheet targetsheet, int sourceRowNum, int copyRowsCount, int destinationRowNum, bool cover) { int[,] sm = getAllMergedRegions(worksheet); List <int[]> copyAreaMergedRegions = new List <int[]>(); //遍历一次源sheet的全部合并单元格 for (int i = 0; i < sm.GetLength(0); i++) { if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount))//18年8月30日发现bug:小于等于改为小于 { copyAreaMergedRegions.Add(new int[4] { sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3] }); } } //int addpoint = destinationRowNum; for (int row = 0; row < copyRowsCount; row++) { //跨sheet复制行 NpoiHelperV1.CopyRowOverSheet(worksheet, targetsheet, sourceRowNum + row, destinationRowNum + row, false, cover); } //将新复制的行进行合并 foreach (int[] cell in copyAreaMergedRegions) { NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress( cell[0] + destinationRowNum, //顶 cell[2] + destinationRowNum, //底 cell[1], //左 cell[3] //右 ); targetsheet.AddMergedRegion(newCellRangeAddress); } }
/// <summary> /// 复制多行并合拼其中的单元格 /// </summary> /// <param name="workbook"></param> /// <param name="worksheet">目标sheet</param> /// <param name="sourceRowNum">起始行号</param> /// <param name="copyRowsCount">复制行数</param> /// <param name="destinationRowNum">目标行号</param> public static void CopyRows(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int copyRowsCount, int destinationRowNum) { int[,] sm = getAllMergedRegions(worksheet); List <int[]> copyAreaMergedRegions = new List <int[]>(); for (int i = 0; i < sm.GetLength(0); i++) { if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount)) { copyAreaMergedRegions.Add(new int[4] { sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3] }); } } int addpoint = destinationRowNum; for (int row = 0; row < copyRowsCount; row++) { //复制行 NpoiHelperV1.CopyRowWithoutMergedRegion(workbook, worksheet, sourceRowNum + row, addpoint); addpoint++; } foreach (int[] cell in copyAreaMergedRegions) { NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress( cell[0] + destinationRowNum, //顶 cell[2] + destinationRowNum, //底 cell[1], //左 cell[3] //右 ); worksheet.AddMergedRegion(newCellRangeAddress); } }
/// <summary> /// 判断当前列是否为简单列 /// </summary> /// <param name="dt"></param> /// <param name="row">最大行索引</param> /// <param name="column">列序号</param> /// <returns></returns> private static bool DataColumnIsSimple(ISheet sheet, DataTable dt, int row, int column, int groups) { if (dt.Rows.Count < row || dt.Columns.Count < column || string.IsNullOrEmpty(dt.Rows[row][column].ToString()) || row == 0) { return(false); } //for (int i = column; i >= 0; i--) //{ // if (dt.Rows[row][column].ToString() != dt.Rows[row - 1][column].ToString()) return false; //} //bool flag = false; if (dt.Rows[row][column].ToString() == dt.Rows[row - 1][column].ToString()) { int numMergedRegions = sheet.NumMergedRegions; if (numMergedRegions == 0 || column == 0) { return(true); } //如果前一列的同一行和上一行处于相同的合并区域内 for (int m = 0; m < numMergedRegions; m++) { NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m); int preCol = column - 1 > 0 ? column - 1 : 0; if (a.FirstColumn <= preCol && a.LastColumn >= preCol && a.FirstRow <= groups + row - 1 && a.LastRow >= row + groups) { return(true); } } } return(false); }
public void CriarCelulaMerge(int primeiraLinha, int ultimaLinha, int primeiraColuna, int ultimaColuna, string nomeAba) { ISheet Sheet = SelecionarAba(nomeAba); var cra = new NPOI.SS.Util.CellRangeAddress(primeiraLinha, ultimaLinha, primeiraColuna, ultimaColuna); Sheet.AddMergedRegion(cra); }
private static ISheet IncluirSubTituloPlanilha(HSSFWorkbook workbook, ISheet sheet, string tituloPlanilha, IRow linha) { var merge = new NPOI.SS.Util.CellRangeAddress(3, 3, 3, 5); sheet.AddMergedRegion(merge); IncluirNaPlanilha(workbook, tituloPlanilha, linha, 16, "Calibri"); return(sheet); }
protected override void SetValue(ICell cell, object source) { NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell); ISheet sheet = cell.Sheet; IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch(); IClientAnchor anchor = region != null? draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) : draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1); draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题 }
private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow) { //int datastartrow = 0; if (!string.IsNullOrEmpty(datadesc)) { NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow); for (int i = 0; i < 10; i++) { desc.CreateCell(i); } NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9); sheet1.AddMergedRegion(address); sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc); datastartrow++; } //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow); int tick = 0; foreach (string value in columnInfo.Values) { row1.CreateCell(tick).SetCellValue(value); tick++; } //将数据逐步写入sheet1各个行 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1); int index = 0; foreach (string key in columnInfo.Keys) { bool isnumberic = false; if (key.ToLower() == "rowid") { rowtemp.CreateCell(index).SetCellValue(i + 1); } else { string data = GetValue(dt.Rows[i], key, out isnumberic); if (isnumberic && data != "--") { rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data)); } else { rowtemp.CreateCell(index).SetCellValue(data); } } index++; } } }
private static ISheet IncluirTextoVLI(HSSFWorkbook workbook, ISheet sheet, IRow linha) { var merge = new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5); sheet.AddMergedRegion(merge); var textoVLI = "VLI - Valor da Logística Integrada"; IncluirNaPlanilha(workbook, textoVLI, linha, 15, "Calibri"); return(sheet); }
/// <summary> /// 获取一个sheet的所有合并单元格 /// </summary> /// <param name="worksheet">目标sheet</param> /// <returns>返回数组结构:起始行,起始列,终止行,终止列;所以第一二个值可用于对该合并格赋值或读取 /// 但该数组的排序根据操作的顺序而定,最新加入的合并单元格为第0个 /// </returns> public static int[,] getAllMergedRegions(ISheet worksheet) { int NumMergedRegions = worksheet.NumMergedRegions; int[,] output = new int[NumMergedRegions, 4]; for (int i = 0; i < worksheet.NumMergedRegions; i++) //NumMergedRegions:整个sheet的合拼单元格数量 { NPOI.SS.Util.CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i); //获取合拼单元格的地址字符串 output[i, 0] = cellRangeAddress.FirstRow; output[i, 1] = cellRangeAddress.FirstColumn; output[i, 2] = cellRangeAddress.LastRow; output[i, 3] = cellRangeAddress.LastColumn; } return(output); }
private void createTableHead(ISheet excelSheet, int baseRow, int baseColumn) { IRow row = excelSheet.CreateRow(baseRow); row.CreateCell(baseColumn).SetCellValue("CIF"); excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn, baseColumn)); row.CreateCell(baseColumn + 1).SetCellValue("Tên KH"); excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn + 1, baseColumn + 1)); row.CreateCell(baseColumn + 2).SetCellValue("BDS"); excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn + 2, baseColumn + 2)); row.CreateCell(baseColumn + 3).SetCellValue("fx"); var cra = new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow, baseColumn + 3, baseColumn + 19); excelSheet.AddMergedRegion(cra); }
private void SetTitleRow(IRow targetRow, string title, int columnsToMerge) { List <ICell> l = new List <ICell>(); for (int i = 0; i < columnsToMerge; i++) { l.Add(targetRow.CreateCell(i)); } var addr = new NPOI.SS.Util.CellRangeAddress(targetRow.RowNum, targetRow.RowNum, 0, columnsToMerge - 1); targetRow.Sheet.AddMergedRegion(addr); var first = l.First(); first.SetCellType(CellType.String); first.SetCellValue(title); }
public static void ExportReportHeader(string title, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { var row = sheet.CreateRow(rowNumber++); row.HeightInPoints = 27; var titleCell = row.CreateCell(0); titleCell.SetCellValue(title); titleCell.CellStyle = allStyles.HeaderStyle; titleCell.CellStyle.WrapText = true; var titleMerge = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7); sheet.AddMergedRegion(titleMerge); row = sheet.CreateRow(rowNumber++); }
static void CreateExportData(List <VendeurData> vendeursData, string fileFullName) { // https://stackoverflow.com/questions/19838743/trying-to-create-a-new-xlsx-file-using-npoi-and-write-to-it using (FileStream stream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write)) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(SHEET_NAME); ICreationHelper cH = wb.GetCreationHelper(); int rowIndex = FIRST_ROW; foreach (var vendeur in vendeursData) { var vendeurCourant = vendeur.VendeurName; IRow rowVendeur = sheet.CreateRow(rowIndex); ICell cellVendeur = rowVendeur.CreateCell(0); cellVendeur.SetCellValue(cH.CreateRichTextString(vendeurCourant)); //merged region https://scottstoecker.wordpress.com/2011/05/18/merging-excel-cells-with-npoi/ NPOI.SS.Util.CellRangeAddress craVendeur = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 4); sheet.AddMergedRegion(craVendeur); rowIndex++; foreach (var clientDataAllYears in vendeur.Clients) { IRow rowClient = sheet.CreateRow(rowIndex); ICell cellClientName = rowClient.CreateCell(1); cellClientName.SetCellValue(cH.CreateRichTextString(clientDataAllYears.Name)); NPOI.SS.Util.CellRangeAddress craClient = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 3); sheet.AddMergedRegion(craClient); InsertCellForYear(cH, clientDataAllYears, rowClient, "2016", 4); InsertCellForYear(cH, clientDataAllYears, rowClient, "2017", 5); InsertCellForYear(cH, clientDataAllYears, rowClient, "2018", 6); rowIndex++; } rowIndex += 2; } wb.Write(stream); } }
public byte[] GetExcelDocument(ExcelMeta excelMeta) { workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet("CARS"); fontWhite = workbook.CreateFont(); fontWhite.Color = IndexedColors.White.Index; fontWhite.IsBold = true; fontBlack = workbook.CreateFont(); fontBlack.IsBold = true; heading1 = GetBasicHeaderStyle(IndexedColors.Grey80Percent.Index, fontWhite, IndexedColors.Grey80Percent.Index); heading2 = GetBasicHeaderStyle(IndexedColors.Grey40Percent.Index, fontBlack, IndexedColors.Grey80Percent.Index); heading2.Alignment = HorizontalAlignment.Left; rowHead = GetBasicHeaderStyle(IndexedColors.White.Index, fontBlack, IndexedColors.Grey80Percent.Index); columnHead = rowHead; content = GetBasicStyle(); for (int index = 0; index < excelMeta.ColumnWidths.Length; index++) { sheet.SetColumnWidth(index, Convert.ToInt32(256 * 20 * (excelMeta.ColumnWidths[index] > 0 ? excelMeta.ColumnWidths[index] : 1))); } for (int r = 0; r < excelMeta.Meta.Count; r++) { IRow row = sheet.GetRow(r) == null?sheet.CreateRow(r) : sheet.GetRow(r); List <ExcelCellMeta> cList = excelMeta.Meta.ElementAt(r); for (int c = 0; c < cList.Count; c++) { List <ICell> placeHoldres = row.Cells; ExcelCellMeta cellMeta = cList.ElementAt(c); if (!placeHoldres.Any(pc => pc.ColumnIndex == cellMeta.ColumnIndex)) { ICell cell = row.CreateCell(cellMeta.ColumnIndex); if (!string.IsNullOrEmpty(cellMeta.Content)) { cell.SetCellValue(cellMeta.Content); } if (GetPropValue(cellMeta.StyleName) != null && GetPropValue(cellMeta.StyleName) is ICellStyle) { cell.CellStyle = GetPropValue(cellMeta.StyleName) as ICellStyle; } else { cell.CellStyle = content; } int spanUptoColumn = cellMeta.ColumnIndex + cellMeta.ColSpan - 1; int spanUptoRow = r + cellMeta.RowSpan - 1; if (cellMeta.ColSpan > 1 && cellMeta.RowSpan <= 1) { for (int s = 1; s < cellMeta.ColSpan; s++) { ICell dummy = row.CreateCell(cellMeta.ColumnIndex + s); dummy.CellStyle = cell.CellStyle; } } else if (cellMeta.RowSpan > 1 && cellMeta.ColSpan <= 1) { for (int s = 1; s < cellMeta.RowSpan; s++) { IRow dummyRow = sheet.CreateRow(r + s); ICell dummy = dummyRow.CreateCell(cellMeta.ColumnIndex); dummy.CellStyle = cell.CellStyle; } } else if (cellMeta.RowSpan > 1 && cellMeta.ColSpan > 1) { for (int cs = 0; cs < cellMeta.ColSpan; cs++) { if (cs != 0) { ICell dummy = row.CreateCell(cellMeta.ColumnIndex + cs); dummy.CellStyle = cell.CellStyle; } for (int rs = 1; rs < cellMeta.RowSpan; rs++) { IRow dummyRow = sheet.GetRow(r + rs) == null?sheet.CreateRow(r + rs) : sheet.GetRow(r + rs); ICell dummy = dummyRow.CreateCell(cellMeta.ColumnIndex + cs); dummy.CellStyle = cell.CellStyle; } } } if (cellMeta.RowSpan > 1 || cellMeta.ColSpan > 1) { NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(r, spanUptoRow, cellMeta.ColumnIndex, spanUptoColumn); sheet.AddMergedRegion(cra); } } } } MemoryStream ms = new MemoryStream(); workbook.Write(ms); byte[] toReturn = ms.ToArray(); return(toReturn); }
private void saveFileDialog1_FileOk(object sender, CancelEventArgs e) { try { var result = _iReportService.GetProductivity(new DateTime(start.Year, start.Month, start.Day, 0, 0, 0), new DateTime(end.Year, end.Month, end.Day, 23, 59, 59)); var quantityViewModel = new List<SaleViewModel>(); foreach (var productivity in result) { var temp = new SaleViewModel(); temp.Sales = _iSaleService.GetQuantity(new DateTime(start.Year, start.Month, start.Day, 0, 0, 0), new DateTime(end.Year, end.Month, end.Day, 23, 59, 59), productivity.Code); temp.Code = productivity.Code; quantityViewModel.Add(temp); } string name = saveFileDialog1.FileName; var wb = new XSSFWorkbook(); // tab name ISheet sheet = wb.CreateSheet("Bao cao SL ban ra hang ngay"); // header IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue("BÁO CÁO SẢN PHẨM HÀNG NGÀY CÔNG TY ATZ"); NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 2); sheet.AddMergedRegion(cra); // column header IRow row3 = sheet.CreateRow(2); ICell cell0 = row3.CreateCell(0); cell0.SetCellValue("STT"); ICell cell1 = row3.CreateCell(1); cell1.SetCellValue("MÃ CHƯƠNG TRÌNH"); ICell cell2 = row3.CreateCell(2); cell2.SetCellValue("CHƯƠNG TRÌNH"); //ICell cell3 = row3.CreateCell(3); //cell3.SetCellValue("DURATION"); ICell cell4 = row3.CreateCell(3); cell4.SetCellValue("CATEGORY"); //ICell cell5 = row3.CreateCell(5); //cell5.SetCellValue("GIÁ SẢN PHẨM"); ICell cell6 = row3.CreateCell(4); cell6.SetCellValue("Ghi chú"); var tempStart = start; var k = 5; while (DateTime.Compare(tempStart, end) <= 0) { ICell cellk = row3.CreateCell(k); cellk.SetCellValue(tempStart.ToString("dd/MM/yyyy")); tempStart = tempStart.AddDays(1); k++; } // add Program Code int i = 3; foreach (var item in result) { var time = Convert.ToDateTime(item.Duration); if (time.Minute > 4) { IRow row_temp = sheet.CreateRow(i); ICell cell_temp0 = row_temp.CreateCell(0); cell_temp0.SetCellValue(i - 2); ICell cell_temp1 = row_temp.CreateCell(1); cell_temp1.SetCellValue(item.Code); ICell cell_temp2 = row_temp.CreateCell(2); cell_temp2.SetCellValue(item.Name); //ICell cell_temp3 = row_temp.CreateCell(3); //DateTime time1 = DateTime.Today; //time1 = time1.AddMinutes(time.Minute).AddSeconds(time.Second); //cell_temp3.SetCellValue(time1); //ICellStyle style = wb.CreateCellStyle(); //cell_temp3.CellStyle = style; //IDataFormat dataFormatCustom = wb.CreateDataFormat(); //cell_temp3.CellStyle.DataFormat = dataFormatCustom.GetFormat("mm:ss"); ICell cell_temp4 = row_temp.CreateCell(3); cell_temp4.SetCellValue(item.Category); //ICell cell_temp5 = row_temp.CreateCell(5); //cell_temp5.SetCellValue(item.Price); ICell cell_temp6 = row_temp.CreateCell(4); cell_temp6.SetCellValue(item.Note); var tempStart1 = start; var k1 = 5; var sales = quantityViewModel.Where(x=> x.Code == item.Code).FirstOrDefault(); while (DateTime.Compare(tempStart1, end) <= 0) { ICell cellk = row_temp.CreateCell(k1); if (sales != null) { var q = sales.Sales.Where(y => y.Date.Year == tempStart1.Year && y.Date.Month == tempStart1.Month && y.Date.Day == tempStart1.Day).FirstOrDefault(); if (q != null) { cellk.SetCellValue(q.Quantity); } else { cellk.SetCellValue(0); } } else { cellk.SetCellValue(0); } tempStart1 = tempStart1.AddDays(1); k1++; } i++; } } for (int l = 0; l < row3.LastCellNum; l++) { sheet.AutoSizeColumn(l); } using (FileStream stream = new FileStream(name, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)) { wb.Write(stream); stream.Close(); } var successForm = new SuccessForm(); successForm.ShowDialog(); } catch (Exception ex) { var errorForm = new ErrorForm(ex.Message); errorForm.ShowDialog(); } }
protected void btnReport_Click(object sender, EventArgs e) { ReportDB _db = new ReportDB(); DataTable formTable = _db.GetMenuItem(pcId); DataSet ds = null; string actualYear = ConfigurationManager.AppSettings["ActualYear"]; ////Create new Excel Workbook var workbook = new HSSFWorkbook(); CellStyle cellStyleBold = workbook.CreateCellStyle(); Font boldFont = workbook.CreateFont(); boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellStyleBold.SetFont(boldFont); foreach (DataRow item in formTable.Rows) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(item[0].ToString()); //Getting reportData ds = new ReportDB().GetReportDataForExcel(pcId, item[0].ToString(), Convert.ToInt32(actualYear), unit); //setting width of cloumns in excel sheet.SetColumnWidth(0, 25 * 256); for (int i = 2; i < ds.Tables[0].Columns.Count; i++) { sheet.SetColumnWidth(i, 15 * 256); } HSSFRow firstRow = (HSSFRow)sheet.CreateRow(0); firstRow.CreateCell(1).SetCellValue(headerContent); firstRow.GetCell(1).CellStyle = cellStyleBold; firstRow.GetCell(1).CellStyle.WrapText = true; //firstRow.GetCell(1).Row.Height = 800; NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5); sheet.AddMergedRegion(cra); HSSFRow thirdRow = (HSSFRow)sheet.CreateRow(2); thirdRow.CreateCell(0).SetCellValue(string.Format(ConfigurationManager.AppSettings["formName"], tfName.ToUpper())); thirdRow.GetCell(0).CellStyle = cellStyleBold; HSSFRow forthRow = (HSSFRow)sheet.CreateRow(3); if (ds.Tables[2].Rows.Count > 0) { forthRow.CreateCell(0).SetCellValue(Convert.ToString(ds.Tables[2].Rows[0][0])); forthRow.GetCell(0).CellStyle = cellStyleBold; } HSSFRow fifthRow = (HSSFRow)sheet.CreateRow(5); fifthRow.CreateCell(1).SetCellValue(GetActualYear(actualYear)); for (int i = 2; i < ds.Tables[1].Rows.Count + 2; i++) { var row = ds.Tables[1].Rows[i - 2]; fifthRow.CreateCell(i).SetCellValue(Convert.ToString(row[0])); } // handling value. int rowIndex = 6; HSSFRow sixthRow = (HSSFRow)sheet.CreateRow(rowIndex); for (int i = 0; i < ds.Tables[0].Columns.Count - 1; i++) { sixthRow.CreateCell(i).SetCellValue(ds.Tables[0].Columns[i].ColumnName); sixthRow.GetCell(i).CellStyle = cellStyleBold; } foreach (DataRow headerRow in ds.Tables[3].Rows) { string filterExp = string.Format("MainHead_tariff_Name='{0}'", headerRow[0]); HSSFRow subHeadRow = (HSSFRow)sheet.CreateRow(++rowIndex); subHeadRow.CreateCell(0).SetCellValue(Convert.ToString(headerRow[0])); subHeadRow.GetCell(0).CellStyle = cellStyleBold; foreach (DataRow row in ds.Tables[0].Select(filterExp)) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(++rowIndex); foreach (DataColumn column in ds.Tables[0].Columns) { if (column.ColumnName == "MainHead_tariff_Name") continue; if (!System.DBNull.Value.Equals(row[column])) { if (column.DataType == typeof(Nullable)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); else if (column.DataType == typeof(DateTime)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDateTime(row[column])); else if (column.DataType == typeof(decimal)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDouble(row[column])); else dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); } } } HSSFRow subTotalRow = (HSSFRow)sheet.CreateRow(++rowIndex); subTotalRow.CreateCell(0).SetCellValue("Sub Total"); subTotalRow.GetCell(0).CellStyle = cellStyleBold; foreach (DataColumn column in ds.Tables[0].Columns) { } for (int i = 1; i < ds.Tables[0].Columns.Count - 1; i++) { if (headerRow[i] != DBNull.Value) { subTotalRow.CreateCell(i).SetCellValue(Convert.ToDouble(headerRow[i])); subTotalRow.GetCell(i).CellStyle = cellStyleBold; } } HSSFRow blankRow = (HSSFRow)sheet.CreateRow(++rowIndex); } } //Write the Workbook to a memory stream MemoryStream output = new MemoryStream(); workbook.Write(output); //return File(output.ToArray(), //The binary data of the XLS file //"application/vnd.ms-excel",//MIME type of Excel files //"ArticleList.xls"); string fileName = "report.xls"; const int bufferLength = 10000; byte[] buffer = new Byte[bufferLength]; int length = 0; if (!string.IsNullOrEmpty(Convert.ToString(Session["centername"]))) { fileName = Convert.ToString(Session["centername"]) + ".xls"; } Response.ContentType = "Application/x-msexcel"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ""); Stream download = null; try { using (FileStream file = new FileStream(Server.MapPath("/Export/" + fileName + ""), FileMode.Create, FileAccess.Write)) { output.WriteTo(file); } download = new FileStream(Server.MapPath("/Export/" + fileName + ""), FileMode.Open, FileAccess.Read); do { if (Response.IsClientConnected) { length = download.Read(buffer, 0, bufferLength); Response.OutputStream.Write(buffer, 0, length); buffer = new Byte[bufferLength]; } else { length = -1; } } while (length > 0); Response.Flush(); Response.End(); } finally { if (download != null) download.Close(); } BindGrid(); }
public async Task <IActionResult> Export([FromQuery] DateTime date) { string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Export"; string d = date.ToShortDateString().Split(@"/").Join(""); string sFileName = @"OrderReport_" + d + @".xlsx"; string URL = string.Format("{0}://{1}/{2}/{3}", Request.Scheme, "localhost:44309", "Export", sFileName); FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); var memory = new MemoryStream(); using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write)) { IWorkbook workbook; workbook = new XSSFWorkbook(); ISheet excelSheet = workbook.CreateSheet(date.ToShortDateString().Split(@"/").Join("")); IRow rowHeader = excelSheet.CreateRow(0); ICellStyle defaultStyle = workbook.CreateCellStyle(); defaultStyle.Alignment = HorizontalAlignment.Center; defaultStyle.VerticalAlignment = VerticalAlignment.Center; defaultStyle.WrapText = true; //wrap the text in the cell defaultStyle.BorderBottom = BorderStyle.Thin; defaultStyle.BorderTop = BorderStyle.Thin; defaultStyle.BorderLeft = BorderStyle.Thin; defaultStyle.BorderRight = BorderStyle.Thin; rowHeader.CreateCell(0).SetCellValue("Hóa đơn"); rowHeader.CreateCell(1).SetCellValue("Khách hàng"); rowHeader.CreateCell(2).SetCellValue("Địa chỉ"); rowHeader.CreateCell(3).SetCellValue("Danh sách mặt hàng"); rowHeader.CreateCell(4).SetCellValue(""); rowHeader.CreateCell(5).SetCellValue(""); rowHeader.CreateCell(6).SetCellValue("Trạng thái"); rowHeader.CreateCell(7).SetCellValue("Tổng giá"); rowHeader.GetCell(0).CellStyle = defaultStyle; rowHeader.GetCell(1).CellStyle = defaultStyle; rowHeader.GetCell(2).CellStyle = defaultStyle; rowHeader.GetCell(3).CellStyle = defaultStyle; rowHeader.GetCell(4).CellStyle = defaultStyle; rowHeader.GetCell(5).CellStyle = defaultStyle; rowHeader.GetCell(6).CellStyle = defaultStyle; rowHeader.GetCell(7).CellStyle = defaultStyle; IRow rowHeader2 = excelSheet.CreateRow(1); rowHeader2.CreateCell(0).SetCellValue(""); rowHeader2.CreateCell(1).SetCellValue(""); rowHeader2.CreateCell(2).SetCellValue(""); rowHeader2.CreateCell(3).SetCellValue("Mặt hàng"); rowHeader2.CreateCell(4).SetCellValue("Số lượng"); rowHeader2.CreateCell(5).SetCellValue("Giá"); rowHeader2.CreateCell(6).SetCellValue(""); rowHeader2.CreateCell(7).SetCellValue(""); rowHeader2.GetCell(0).CellStyle = defaultStyle; rowHeader2.GetCell(1).CellStyle = defaultStyle; rowHeader2.GetCell(2).CellStyle = defaultStyle; rowHeader2.GetCell(3).CellStyle = defaultStyle; rowHeader2.GetCell(4).CellStyle = defaultStyle; rowHeader2.GetCell(5).CellStyle = defaultStyle; rowHeader2.GetCell(6).CellStyle = defaultStyle; rowHeader2.GetCell(7).CellStyle = defaultStyle; var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 3, 5); var cr1 = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); var cr2 = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); var cr3 = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); var cr4 = new NPOI.SS.Util.CellRangeAddress(0, 1, 6, 6); var cr5 = new NPOI.SS.Util.CellRangeAddress(0, 1, 7, 7); excelSheet.AddMergedRegion(cra); excelSheet.AddMergedRegion(cr1); excelSheet.AddMergedRegion(cr2); excelSheet.AddMergedRegion(cr3); excelSheet.AddMergedRegion(cr4); excelSheet.AddMergedRegion(cr5); excelSheet.SetColumnWidth(0, 8000); excelSheet.SetColumnWidth(1, 6000); excelSheet.SetColumnWidth(2, 9000); excelSheet.SetColumnWidth(3, 6000); //in ra danh sach cac khach hang IEnumerable <CustomerModel> customers = _CustomerAppService.All(); SearchInvoiceRequest request = new SearchInvoiceRequest() { customer_code = null, customer_id = 0, from_time = date, page = 0, page_size = 0 }; var result = await _InvoiceAppService.getAllInvoice(request); if (result.Data == null) { return(BadRequest()); } int rowIndex = 2; int startRowIndex = 2; string address = ""; foreach (InvoiceModel invoice in result.Data) { startRowIndex = rowIndex; IRow row = excelSheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(invoice.Code); row.CreateCell(1).SetCellValue(invoice.CustomerName); address = invoice.Address == null ? "" : invoice.Address.StreetNumber + ", " + invoice.Address.Street + ", " + invoice.Address.District + ", " + invoice.Address.City + ", " + invoice.Address.Country; row.CreateCell(2).SetCellValue(address); row.CreateCell(6).SetCellValue(Common.Common.GetStatusInvoice(invoice.Status)); row.CreateCell(7).SetCellValue(invoice.TotalPrice); foreach (InvoiceItemModel item in invoice.Items) { if (rowIndex != startRowIndex) { row = excelSheet.CreateRow(rowIndex); row.CreateCell(0).CellStyle = defaultStyle; row.CreateCell(1).CellStyle = defaultStyle; row.CreateCell(2).CellStyle = defaultStyle; row.CreateCell(6).CellStyle = defaultStyle; row.CreateCell(7).CellStyle = defaultStyle; } row.CreateCell(3).SetCellValue(item.ProductName); row.GetCell(3).CellStyle = defaultStyle; row.CreateCell(4).SetCellValue(item.Quantity + " " + item.UnitName); row.GetCell(4).CellStyle = defaultStyle; row.CreateCell(5).SetCellValue(item.TotalPrice); row.GetCell(5).CellStyle = defaultStyle; rowIndex++; } var cra1 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 0, 0); var cra2 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 1, 1); var cra3 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 2, 2); var cra4 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 6, 6); var cra5 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 7, 7); excelSheet.AddMergedRegion(cra1); excelSheet.AddMergedRegion(cra2); excelSheet.AddMergedRegion(cra3); excelSheet.AddMergedRegion(cra4); excelSheet.AddMergedRegion(cra5); IRow rowStart = excelSheet.GetRow(startRowIndex); rowStart.GetCell(0).CellStyle = defaultStyle; rowStart.GetCell(1).CellStyle = defaultStyle; rowStart.GetCell(2).CellStyle = defaultStyle; rowStart.GetCell(6).CellStyle = defaultStyle; rowStart.GetCell(7).CellStyle = defaultStyle; } workbook.Write(fs); } using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open)) { await stream.CopyToAsync(memory); } memory.Position = 0; //FileStreamResult f = File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName); return(Ok(URL)); }
/// <summary> /// 将DataTable转为NPOI工作簿时添加表数据 /// </summary> /// <param name="sheet"></param> /// <param name="dt"></param> /// <param name="cellStyle"></param> /// <param name="colTypeList">每一列的数据类型</param> /// <param name="groups">表头行数(作为数据行第一行的索引)</param> /// <param name="maxColumnMerge">Excel所有非表头区域的合并单元格的最大列索引,若不启用则传人-1</param> private static void AddSheetBody(ISheet sheet, DataTable dt, ICellStyle cellStyle, List <int> colTypeList, int groups, int maxColumnMerge) { IRow row; ICell cell; ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook); ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook); for (int i = 0; i < dt.Rows.Count; i++) { row = sheet.GetRow(i + groups); for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j] == null || string.IsNullOrEmpty(dt.Rows[i][j].ToString())) { continue; } #region 当列索引小于最大合并列索引时判断单元格是否处于合并区域内 //当列索引小于最大合并列索引时判断单元格是否处于合并区域内 bool flagSkip = false; if (j <= maxColumnMerge) { for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域 { NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m); if (a.LastRow < groups)//剔除标题处的合并区域 { continue; } //当前单元格是处于合并区域内且不为合并区域第一个单元格时,跳过此单元格 if (a.FirstRow < i + groups && a.LastRow > i + groups) { flagSkip = true; //Debug.WriteLine("第" + i.ToString() + "行" + j.ToString() + "列被跳过"); break; } } } if (flagSkip) { continue; } #endregion //创建单元格 cell = row.CreateCell(j); if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型 { cell.SetCellValue(dt.Rows[i][j].ToString()); cell.CellStyle = cellStyle; } else { string cellText = dt.Rows[i][j].ToString(); try { switch (colTypeList[j]) { case 1: cell.SetCellValue(int.Parse(cellText)); //int类型 cell.CellStyle = cellStyle; break; case 2: cell.SetCellValue(double.Parse(cellText)); //decimal数据类型 cell.CellStyle = cellStyleDecimal; break; case 3: cell.SetCellValue(DateTime.Parse(cellText)); //日期类型 cell.CellStyle = cellStyleDateTime; break; default: cell.SetCellValue(cellText); cell.CellStyle = cellStyle; break; } } catch { cell.SetCellValue("单元格导出失败"); } } } } }
private void generate_my_data() { ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//* DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty); DataTable table = view.ToTable(); DataTable dT = (DataTable)Session["header_01"]; table.TableName = "Załatwienia"; table.Columns.Remove("id_"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0); IRow row0 = sheet0.CreateRow(0); #region tabela1 foreach (DataRow dR in dr.getData(dT, "Column1=3")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4); sheet0.AddMergedRegion(crs); } } row0 = sheet0.CreateRow(1); foreach (DataRow dR in dr.getData(dT, "Column1=2")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4); sheet0.AddMergedRegion(crs); } } row0 = sheet0.CreateRow(2); foreach (DataRow dR in dr.getData(dT, "Column1=1")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4); sheet0.AddMergedRegion(crs); } } int rol = 3; foreach (DataRow rowik in table.Rows) { row0 = sheet0.CreateRow(rol); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row0.CreateCell(i).SetCellValue(ji); row0.Cells[i].CellStyle = cellStyle; } catch (Exception) { row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach #endregion tabela1 #region drugi arkusz // druga tabela view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.TableName = "Załatwienia"; table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); // //robienie int ro = 2; //----------------- IDataFormat format = hssfworkbook.CreateDataFormat(); ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia"); IRow row2 = sheet1.CreateRow(0); dT.Clear(); dT = (DataTable)Session["header_02"]; //=========== foreach (DataRow dR in dr.getData(dT, "Column1=3")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4); sheet0.AddMergedRegion(crs); } } row2 = sheet0.CreateRow(1); foreach (DataRow dR in dr.getData(dT, "Column1=2")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4); sheet0.AddMergedRegion(crs); } } row2 = sheet0.CreateRow(2); foreach (DataRow dR in dr.getData(dT, "Column1=1")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4); sheet0.AddMergedRegion(crs); } } rol = 3; foreach (DataRow rowik in table.Rows) { row2 = sheet0.CreateRow(rol); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach foreach (DataRow rowik in table.Rows) { row2 = sheet1.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach #endregion drugi arkusz // trzeci sheet view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); sheet1.AutoSizeColumn(0, true); sheet1.AutoSizeColumn(1, true); ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia"); row2 = sheet2.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet2.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Wyznaczenia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet2.AddMergedRegion(crs); row2 = sheet2.CreateRow(1); row2.CreateCell(5).SetCellValue("GU bez ''of''"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("GU ''of''"); row2.CreateCell(8).SetCellValue("GU Razem"); row2.CreateCell(9).SetCellValue("GUp bez '''of'"); row2.CreateCell(10).SetCellValue("GUp ''of''"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet2.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // czwarty sheet view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); //table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_09"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów"); row2 = sheet3.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet3.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet3.AddMergedRegion(crs); row2 = sheet3.CreateRow(1); row2.CreateCell(5).SetCellValue("GU bez ''of''"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("GU ''of''"); row2.CreateCell(8).SetCellValue("GU Razem"); row2.CreateCell(9).SetCellValue("GUp bez '''of'"); row2.CreateCell(10).SetCellValue("GUp ''of''"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); // row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet3.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach }
public string dataTableToExcel(System.Data.DataTable dt,string title,string subject,string category,string company,string author) { Microsoft.Win32.SaveFileDialog saveFileDialog1 = new Microsoft.Win32.SaveFileDialog(); //设置文件类型 saveFileDialog1.Filter = " Excel files(*.xls)|*.xls|All files(*.*)|*.*"; //设置默认文件类型显示顺序 saveFileDialog1.FilterIndex = 1; //保存对话框是否记忆上次打开的目录 saveFileDialog1.RestoreDirectory = true; string localFilePath = ""; if (!saveFileDialog1.ShowDialog() == true) { return null; } //创建一个新的xls文件 HSSFWorkbook workbook = new HSSFWorkbook(); //创建DocumentSummaryInformation(选填) DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = company;// "国家电网XXXXX公司"; dsi.Category = category;// "报表"; //创建SummaryInformation(选填) SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = subject;// "商品列表"; si.Title = title; si.Author = author;// "Kevin Pan"; //把创建好的对象赋给Workbook workbook.DocumentSummaryInformation = dsi; workbook.SummaryInformation = si; //创建一个Sheet ISheet sheet = workbook.CreateSheet("Sheet1"); sheet.DefaultRowHeight = 300; //创建标题 IRow rowTitle = sheet.CreateRow(0); rowTitle.Height = 500; ICellStyle styleTitle = workbook.CreateCellStyle(); styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; IFont fontTitle = workbook.CreateFont(); fontTitle.FontName = "宋体"; fontTitle.FontHeightInPoints = 18; styleTitle.SetFont(fontTitle); //创建表格样式 IFont font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; ICellStyle style = workbook.CreateCellStyle(); ; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font); // 标题 ICell cellTitle = rowTitle.CreateCell(0); cellTitle.SetCellValue(title); cellTitle.CellStyle = styleTitle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0,dt.Columns.Count)); //合并单元格 //创建表头 IRow rowHead = sheet.CreateRow(1); rowHead.CreateCell(0).SetCellValue("序号"); rowHead.GetCell(0).CellStyle = style; sheet.SetColumnWidth(0, 256 * 5); for(int i =0; i< dt.Columns.Count;i++) { rowHead.CreateCell(i+1).SetCellValue(dt.Columns[i].ColumnName); rowHead.GetCell(i+1).CellStyle = style; sheet.SetColumnWidth(i+1, 256 * 20); } sheet.SetColumnWidth(1, 256 * 30); sheet.SetColumnWidth(6, 256 * 25); //获取商品列表数据 //List<ProductModel> dataList = GetProductList(); //绑定表内容 string last_id = ""; int last_row = 0; for (int i = 0; i < dt.Rows.Count; i++) { IRow rowContent = sheet.CreateRow(i+2); rowContent.CreateCell(0).SetCellValue(i+1); rowContent.GetCell(0).CellStyle = style; //string id = dt.Rows[1][dt.Columns[0].ColumnName].ToString(); if (last_id.Equals(dt.Rows[i][dt.Columns[0].ColumnName].ToString())) { //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(last_row, i+2, 1, 1)); //string v = sheet.GetRow(last_row).GetCell(1).RichStringCellValue.ToString(); //sheet.GetRow(last_row).GetCell(1).SetCellValue(""); NPOI.SS.Util.CellRangeAddress r = new NPOI.SS.Util.CellRangeAddress(last_row, i + 2, 1, 1); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i+1, i + 2, 1, 1)); //sheet.GetRow(last_row).GetCell(1).SetCellValue(v); } else { rowContent.CreateCell(1).SetCellValue(dt.Rows[i][dt.Columns[0].ColumnName].ToString()); rowContent.GetCell(1).CellStyle = style; last_id = dt.Rows[i][dt.Columns[0].ColumnName].ToString(); last_row = i + 2; } for(int j =1; j< dt.Columns.Count;j++) { rowContent.CreateCell(j+1).SetCellValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString()); rowContent.GetCell(j+1).CellStyle = style; } } //输出 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); //File f = new File(ms, "application/vnd.ms-excel", "商品列表.xls"); /// // Microsoft.Win32.SaveFileDialog saveFileDialog1 = new Microsoft.Win32.SaveFileDialog(); //设置文件类型 //saveFileDialog1.Filter = " Excel files(*.xls)|*.xls|All files(*.*)|*.*"; //设置默认文件类型显示顺序 // saveFileDialog1.FilterIndex = 1; //保存对话框是否记忆上次打开的目录 // saveFileDialog1.RestoreDirectory = true; //string localFilePath = ""; //点了保存按钮进入 //获得文件路径 localFilePath = saveFileDialog1.FileName.ToString(); //获取文件名,不带路径 string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件路径,不带文件名 //FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\")); //给文件名前加上时间 //newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt; //在文件名里加字符 //saveFileDialog1.FileName.Insert(1,"dameng"); //// try { FileStream fs = File.OpenWrite(@localFilePath); workbook.Write(fs); fs.Close(); }catch(FileLoadException e) { ;// } //ConfirmDialog m = new ConfirmDialog(); //m.Show(); //m.ShowDialog(); return localFilePath; }
public CellRangeAddressTreeNode(NPOI.SS.Util.CellRangeAddress record) { this.Record = record; this.Text = record.GetType().Name; this.ImageKey = "Binary"; }
public static IRow CopyRow(this IRow dstRow, ISheet srcSheet, int srcRowNum) { IRow sourceRow = srcSheet.GetRow(srcRowNum) as IRow; ICell oldCell, newCell; int i; // Loop through source columns to add to new row for (i = 0; i < sourceRow.LastCellNum; i++) { // Grab a copy of the old/new cell oldCell = sourceRow.GetCell(i) as ICell; newCell = dstRow.GetCell(i) as ICell; if (newCell == null) { newCell = dstRow.CreateCell(i) as ICell; } // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell newCell.CellStyle = oldCell.CellStyle; // If there is a cell comment, copy if (newCell.CellComment != null) { newCell.CellComment = oldCell.CellComment; } // If there is a cell hyperlink, copy if (oldCell.Hyperlink != null) { newCell.Hyperlink = oldCell.Hyperlink; } // Set the cell data value switch (oldCell.CellType) { case CellType.Blank: newCell.SetCellValue(oldCell.StringCellValue); break; case CellType.Boolean: newCell.SetCellValue(oldCell.BooleanCellValue); break; case CellType.Error: newCell.SetCellErrorValue(oldCell.ErrorCellValue); break; case CellType.Formula: newCell.CellFormula = oldCell.CellFormula; break; case CellType.Numeric: newCell.SetCellValue(oldCell.NumericCellValue); break; case CellType.String: newCell.SetCellValue(oldCell.RichStringCellValue); break; case CellType.Unknown: newCell.SetCellValue(oldCell.StringCellValue); break; } } //If there are are any merged regions in the source row, copy to new row NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null; for (i = 0; i < srcSheet.NumMergedRegions; i++) { cellRangeAddress = srcSheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow == sourceRow.RowNum) { newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(dstRow.RowNum, (dstRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)), cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn); dstRow.Sheet.AddMergedRegion(newCellRangeAddress); } } //複製行高到新列 //if (copyRowHeight) dstRow.Height = sourceRow.Height; ////重製原始列行高 //if (resetOriginalRowHeight) // sourceRow.Height = worksheet.DefaultRowHeight; ////清掉原列 //if (IsRemoveSrcRow == true) // worksheet.RemoveRow(sourceRow); return(dstRow); }
/// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); }
/// <summary> /// 将DataTable各列中相邻行值相同的单元格合并显示 /// </summary> /// <param name="sheet"></param> /// <param name="dt"></param> /// <param name="groupCount">NPOI表头行数</param> /// <returns>被合并的列的最大索引值</returns> private static int DataTableMergSimpleValueRow(ISheet sheet, DataTable dt, int groupCount) { if (dt.Rows.Count == 0 || dt.Columns.Count == 0) { return(0); } int maxColumnMerge = 0;//被合并的列的最大索引值 for (int ColumnIndex = 0; ColumnIndex < dt.Columns.Count; ColumnIndex++) { #region 是否停止添加合并区域 bool flagSkip = true; if (ColumnIndex == 0)//第一列始终需要合并 { flagSkip = false; } else { for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域 { NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m); if (a.LastRow < groupCount)//剔除标题处的合并区域 { continue; } //当存在最大列包含上一列的合并区域时,当前列仍为可合并状态 if (a.LastColumn >= maxColumnMerge) { flagSkip = false; break; } } } if (flagSkip)//当前列的前一列不包括任何合并行时,停止后续列的行合并 { return(maxColumnMerge - 1 >= 0 ? maxColumnMerge - 1 : 0); } #endregion int rowspan = 0; for (int i = dt.Rows.Count - 1; i >= 0; i--) { if (DataColumnIsSimple(sheet, dt, i, ColumnIndex, groupCount)) { rowspan++; } else if (rowspan > 0) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount + i, groupCount + i + rowspan, ColumnIndex, ColumnIndex)); rowspan = 0; } } if (rowspan > 1)//行数超过一行时第一列完全一致 { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount, groupCount + rowspan + 1, ColumnIndex, ColumnIndex)); } maxColumnMerge = ColumnIndex; } if (maxColumnMerge == dt.Columns.Count - 1) { return(maxColumnMerge); } return(maxColumnMerge - 1); }
private void CargaHojaExcel(DataTable tablaExcel, string titulo, string nombreHoja, bool incluirGrafico) { #region --[Estilos]-- IFont fuenteTitulo = excelFile.CreateFont(); fuenteTitulo.FontName = "Calibri"; //fuenteTitulo.FontHeight = (short)FontSize.Large.GetHashCode(); fuenteTitulo.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode(); IFont unaFuente = excelFile.CreateFont(); unaFuente.FontName = "Tahoma"; //unaFuente.FontHeight = (short)FontSize.Medium.GetHashCode(); IFont fuenteEncabezado = excelFile.CreateFont(); fuenteEncabezado.FontName = "Tahoma"; //fuenteEncabezado.FontHeight = (short)FontSize.Medium.GetHashCode(); ; fuenteEncabezado.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode(); ICellStyle unEstiloDecimal = excelFile.CreateCellStyle(); IDataFormat format = excelFile.CreateDataFormat(); unEstiloDecimal.DataFormat = format.GetFormat("0.00"); unEstiloDecimal.SetFont(unaFuente); #endregion #region --[Hoja]-- ISheet hojaExcel = excelFile.CreateSheet(nombreHoja); NPOI.SS.Util.CellRangeAddress rango = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tablaExcel.Columns.Count - 1); hojaExcel.AddMergedRegion(rango); hojaExcel.AutoSizeColumn(0); hojaExcel.AutoSizeColumn(1); int idxAux = 0; IRow fila = hojaExcel.CreateRow(idxAux); fila.CreateCell(idxAux).SetCellValue(titulo); fila.Cells[idxAux].CellStyle.SetFont(fuenteTitulo); idxAux++; fila = hojaExcel.CreateRow(idxAux); idxAux++; RptIndicadores alumno = null; int idAlumno = 0; string nombre = string.Empty; hojaExcel.AutoSizeColumn(0); //--Agrego los encabezados-- #region --[Encabezados]-- for (int i = 0; i < tablaExcel.Columns.Count; i++) { fila.CreateCell(i).CellStyle.Alignment = HorizontalAlignment.CENTER; nombre = tablaExcel.Columns[i].ColumnName; alumno = new RptIndicadores(); int.TryParse(tablaExcel.Columns[i].ColumnName, out idAlumno); if (idAlumno > 0) { alumno = lista.Find(p => p.idAlumno == Convert.ToInt16(idAlumno)); nombre = alumno.alumnoApellido.Trim() + " " + alumno.alumnoNombre.Trim(); } fila.CreateCell(i).SetCellValue(nombre); fila.Cells[i].CellStyle.SetFont(fuenteEncabezado); hojaExcel.AutoSizeColumn(i); } #endregion //--Agrego los datos hoja-- #region --[Datos]-- decimal valorDato = 0; int cantFilas = 0; try { for (int i = 0; i < tablaExcel.Rows.Count; i++) { fila = hojaExcel.CreateRow(idxAux); idxAux++; cantFilas++; for (int j = 0; j < tablaExcel.Columns.Count; j++) { try { valorDato = decimal.Parse(tablaExcel.Rows[i][j].ToString()); fila.CreateCell(j).SetCellValue(Convert.ToDouble(valorDato)); fila.Cells[j].CellStyle = unEstiloDecimal; fila.Cells[j].SetCellType(CellType.NUMERIC); } catch { fila.CreateCell(j).SetCellValue(tablaExcel.Rows[i][j].ToString()); fila.Cells[j].CellStyle.SetFont(fuenteEncabezado); } } } } catch (Exception ex) { throw ex; } #endregion //Acomodo las columnas for (int j = 0; j < tablaExcel.Columns.Count; j++) { hojaExcel.AutoSizeColumn(j); } hojaExcel.AutoSizeColumn(0); #endregion if (incluirGrafico) { #region --[Grafico]-- //map the path to the img folder //string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), "img"); string imagesPath = System.IO.Path.GetFullPath(NombrePNG); //grab the image file //create an image from the path System.Drawing.Image image = System.Drawing.Image.FromFile(imagesPath); MemoryStream ms = new MemoryStream(); //pull the memory stream from the image (I need this for the byte array later) image.Save(ms, System.Drawing.Imaging.ImageFormat.Png); //the drawing patriarch will hold the anchor and the master information var patriarch = hojaExcel.CreateDrawingPatriarch(); //store the coordinates of which cell and where in the cell the image goes HSSFClientAnchor anchor = new HSSFClientAnchor(20, 0, 40, 20, 3, cantFilas + 3, 4, cantFilas + 10); //types are 0, 2, and 3. 0 resizes within the cell, 2 doesn't anchor.AnchorType = 2; //add the byte array and encode it for the excel file int index = excelFile.AddPicture(ms.ToArray(), PictureType.PNG); var picture = patriarch.CreatePicture(anchor, index); picture.Resize(); picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL; hojaExcel.ForceFormulaRecalculation = true; #endregion } }
public void FillReg17SpreadSheet(Dictionary <string, Reg17Record> records) { xssfwb = new XSSFWorkbook(); XSSFFont myFont = (XSSFFont)xssfwb.CreateFont(); myFont.FontHeightInPoints = 12; myFont.Boldweight = (short)FontBoldWeight.Bold; //myFont.FontName = "Tahoma"; XSSFFont myFont2 = (XSSFFont)xssfwb.CreateFont(); myFont2.FontHeightInPoints = 18; myFont2.Boldweight = (short)FontBoldWeight.Bold; //myFont2.FontName = "Tahoma"; IFont boldFont = xssfwb.CreateFont(); boldFont.Boldweight = (short)FontBoldWeight.Bold; var color = new XSSFColor(new byte[] { 196, 215, 155 }); XSSFCellStyle borderedCellStyle = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle.SetFont(myFont); borderedCellStyle.VerticalAlignment = VerticalAlignment.Center; XSSFCellStyle borderedCellStyle2 = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle2.SetFont(myFont2); borderedCellStyle2.VerticalAlignment = VerticalAlignment.Center; borderedCellStyle2.Alignment = HorizontalAlignment.Center; borderedCellStyle2.SetFillForegroundColor(color); borderedCellStyle2.SetFillBackgroundColor(color); borderedCellStyle2.FillBackgroundXSSFColor = color; borderedCellStyle2.FillForegroundXSSFColor = color; borderedCellStyle2.FillPattern = FillPattern.SolidForeground; XSSFCellStyle borderedCellStyle3 = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle3.SetFont(myFont); borderedCellStyle3.VerticalAlignment = VerticalAlignment.Center; borderedCellStyle3.Alignment = HorizontalAlignment.Center; borderedCellStyle3.SetFillForegroundColor(color); borderedCellStyle3.SetFillBackgroundColor(color); borderedCellStyle3.FillBackgroundXSSFColor = color; borderedCellStyle3.FillForegroundXSSFColor = color; borderedCellStyle3.FillPattern = FillPattern.SolidForeground; borderedCellStyle3.WrapText = true; ISheet Sheet = xssfwb.CreateSheet("Report"); Sheet.SetColumnWidth(0, 22 * 256); Sheet.SetColumnWidth(1, 17 * 256); Sheet.SetColumnWidth(2, 25 * 256); Sheet.SetColumnWidth(3, 15 * 256); Sheet.SetColumnWidth(4, 96 * 256); Sheet.SetColumnWidth(5, 35 * 256); //Creat The Headers of the excel IRow row1 = Sheet.CreateRow(0); //styling ICellStyle boldStyle = xssfwb.CreateCellStyle(); boldStyle.SetFont(boldFont); //Create The Actual Cells row1.CreateCell(0).SetCellValue("Sistema PCI DSS"); row1.GetCell(0).CellStyle = boldStyle; IRow row2 = Sheet.CreateRow(1); row2.CreateCell(0).SetCellValue("REG-17"); row2.GetCell(0).CellStyle = boldStyle; IRow row3 = Sheet.CreateRow(3); row3.CreateCell(0).SetCellValue("Informe de Aplicación de Parches"); row3.GetCell(0).CellStyle = borderedCellStyle2; var cra = new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 5); Sheet.AddMergedRegion(cra); row2.CreateCell(0).SetCellValue("REG-17"); row2.GetCell(0).CellStyle = boldStyle; IRow row4 = Sheet.CreateRow(4); row4.CreateCell(0).SetCellValue("Servidores"); row4.GetCell(0).CellStyle = borderedCellStyle; row4.CreateCell(1).SetCellValue("SQLPCI"); row4.GetCell(1).CellStyle = boldStyle; row4.CreateCell(2).SetCellValue("Fecha de Aplicación"); row4.GetCell(2).CellStyle = borderedCellStyle; row4.CreateCell(3).SetCellValue(DateTime.Now.ToString()); row4.GetCell(3).CellStyle = borderedCellStyle; IRow row5 = Sheet.CreateRow(5); row5.CreateCell(1).SetCellValue("SRVFILE"); row5.GetCell(1).CellStyle = boldStyle; IRow row6 = Sheet.CreateRow(6); row6.CreateCell(1).SetCellValue("Site-Transfer"); row6.GetCell(1).CellStyle = boldStyle; IRow row7 = Sheet.CreateRow(7); row7.CreateCell(1).SetCellValue("WEBPCI"); row7.GetCell(1).CellStyle = boldStyle; IRow row8 = Sheet.CreateRow(8); row8.CreateCell(1).SetCellValue("Proxy Inverso"); row8.GetCell(1).CellStyle = boldStyle; IRow row9 = Sheet.CreateRow(9); row9.CreateCell(1).SetCellValue("Proxy Servicios"); row9.GetCell(1).CellStyle = boldStyle; IRow row10 = Sheet.CreateRow(10); row10.CreateCell(1).SetCellValue("Proxy Web"); row10.GetCell(1).CellStyle = boldStyle; IRow row11 = Sheet.CreateRow(11); row11.CreateCell(1).SetCellValue("Parches"); row11.GetCell(1).CellStyle = boldStyle; IRow row12 = Sheet.CreateRow(12); row12.CreateCell(1).SetCellValue("ADMPCI"); row12.GetCell(1).CellStyle = boldStyle; IRow row13 = Sheet.CreateRow(13); row13.CreateCell(1).SetCellValue("File Gateway"); row13.GetCell(1).CellStyle = boldStyle; IRow row14 = Sheet.CreateRow(14); row14.CreateCell(1).SetCellValue("Volume Gateway"); row14.GetCell(1).CellStyle = boldStyle; IRow row15 = Sheet.CreateRow(15); row15.CreateCell(1).SetCellValue("RD Gateway"); row15.GetCell(1).CellStyle = boldStyle; IRow row16 = Sheet.CreateRow(16); row16.CreateCell(1).SetCellValue("Data Analysis"); row16.GetCell(1).CellStyle = boldStyle; IRow row18 = Sheet.CreateRow(18); row18.CreateCell(0).SetCellValue("Código Parche"); row18.GetCell(0).CellStyle = borderedCellStyle3; row18.CreateCell(1).SetCellValue("Fecha Publicación"); row18.GetCell(1).CellStyle = borderedCellStyle3; row18.CreateCell(2).SetCellValue("Producto"); row18.GetCell(2).CellStyle = borderedCellStyle3; row18.CreateCell(3).SetCellValue("Clasificación"); row18.GetCell(3).CellStyle = borderedCellStyle3; row18.CreateCell(4).SetCellValue("Mejoras y Correcciones"); row18.GetCell(4).CellStyle = borderedCellStyle3; row18.CreateCell(5).SetCellValue("Opinión Impacto del Parche"); row18.GetCell(5).CellStyle = borderedCellStyle3; XSSFFont myFont4 = (XSSFFont)xssfwb.CreateFont(); myFont4.FontHeightInPoints = 10; //myFont4.Boldweight = (short)FontBoldWeight.Bold; //myFont.FontName = "Tahoma"; XSSFFont myFont5 = (XSSFFont)xssfwb.CreateFont(); myFont5.FontHeightInPoints = 8.5; //myFont5.Boldweight = (short)FontBoldWeight.Bold; //myFont2.FontName = "Tahoma"; boldFont.Boldweight = (short)FontBoldWeight.Bold; XSSFCellStyle borderedCellStyle4 = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle4.SetFont(myFont4); borderedCellStyle4.VerticalAlignment = VerticalAlignment.Center; borderedCellStyle4.Alignment = HorizontalAlignment.Center; XSSFCellStyle borderedCellStyle5 = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle5.SetFont(myFont5); borderedCellStyle5.VerticalAlignment = VerticalAlignment.Center; //borderedCellStyle5.Alignment = HorizontalAlignment.Center; borderedCellStyle5.WrapText = true; XSSFCellStyle borderedCellStyle6 = (XSSFCellStyle)xssfwb.CreateCellStyle(); borderedCellStyle6.SetFont(myFont4); borderedCellStyle6.VerticalAlignment = VerticalAlignment.Center; //borderedCellStyle6.Alignment = HorizontalAlignment.Center; borderedCellStyle6.WrapText = true; int i = 19; //List<Reg17Record> records = new List<Reg17Record>(); foreach (KeyValuePair <string, Reg17Record> entry in records) { // do something with entry.Value or entry.Key Reg17Record record = (Reg17Record)entry.Value; IRow rowi = Sheet.CreateRow(i); rowi.CreateCell(0).SetCellValue(record.PatchCode); rowi.GetCell(0).CellStyle = borderedCellStyle4; rowi.CreateCell(1).SetCellValue(record.PublicationDate); rowi.GetCell(1).CellStyle = borderedCellStyle4; rowi.CreateCell(2).SetCellValue(record.Product); rowi.GetCell(2).CellStyle = borderedCellStyle4; rowi.CreateCell(3).SetCellValue(record.Classification); rowi.GetCell(3).CellStyle = borderedCellStyle4; rowi.CreateCell(4).SetCellValue(record.EnhancementsAndCorrections); rowi.GetCell(4).CellStyle = borderedCellStyle5; rowi.CreateCell(5).SetCellValue(record.ImpactOpinion); rowi.GetCell(5).CellStyle = borderedCellStyle6; i++; } }
/// <summary> /// 为Excel添加表头 /// </summary> /// <param name="sheet"></param> /// <param name="headerRow">GridView的HeaderRow属性</param> /// <param name="headerCellStyle">表头格式</param> /// <param name="flagNewLine">转行标志</param> /// <param name="colCount">Excel表列数</param> /// <returns>Excel表格行数</returns> private static int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount) { //int colCount = 0; //记录GridView列数 int rowInex = 0; //记录表头的行数 IRow row = sheet.CreateRow(0); ICell cell; int groupCount = 0; //记录分组数 int colIndex = 0; //记录列索引,并于结束表头遍历后记录总列数 for (int i = 0; i < headerRow.Cells.Count; i++) { if (rowInex != groupCount)//新增了标题行时重新创建 { row = sheet.CreateRow(rowInex); groupCount = rowInex; } #region 是否跳过当前单元格 for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域 { NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m); //当前单元格是处于合并区域内 if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex && a.FirstRow <= rowInex && a.LastRow >= rowInex) { colIndex++; m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域 } } #endregion cell = row.CreateCell(colIndex); cell.CellStyle = headerCellStyle; TableCell tablecell = headerRow.Cells[i]; //跨列属性可能为添加了html属性colspan,也可能是由cell的ColumnSpan属性指定 int colSpan = 0; int rowSpan = 0; #region 获取跨行跨列属性值 //跨列 if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"])) { colSpan = int.Parse(tablecell.Attributes["colspan"].ToString()); colSpan--; } if (tablecell.ColumnSpan > 1) { colSpan = tablecell.ColumnSpan; colSpan--; } //跨行 if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"])) { rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString()); rowSpan--; } if (tablecell.RowSpan > 1) { rowSpan = tablecell.RowSpan; rowSpan--; } #endregion //添加excel合并区域 if (colSpan > 0 || rowSpan > 0) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan)); colIndex += colSpan + 1;//重新设置列索引 } else { colIndex++; } string strHeader = headerRow.Cells[i].Text; if (strHeader.Contains(flagNewLine))//换行标记,当只存在一行标题时不存在</th></tr><tr>,此时colCount无法被赋值 { rowInex++; colCount = colIndex; colIndex = 0; strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>")); } cell.SetCellValue(strHeader); } if (groupCount == 0)//只有一行标题时另外为colCount赋值 { colCount = colIndex; } rowInex++;//表头结束后另起一行开始记录控件数据行索引 ICellStyle _headrStyle = GetCellStyleHead(sheet.Workbook); for (int i = 0; i < rowInex; i++) { for (int j = 0; j < colCount; j++) { if (sheet.GetRow(i).GetCell(j) != null) { sheet.GetRow(i).GetCell(j).CellStyle = _headrStyle; } } } return(rowInex); }
private int _addMonthlyInvoices(ref ISheet accountSheet, int rowNumber, Account account, int monthIndex, string monthName) { ICell cell; var startRow = rowNumber; var minRows = 30; var headerStyle = _workbook.CreateCellStyle(); headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Maroon.Index; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.Alignment = HorizontalAlignment.Center; headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.BorderTop = BorderStyle.Thin; headerStyle.BorderLeft = BorderStyle.Thin; headerStyle.BorderRight = BorderStyle.Thin; var headerFont = _workbook.CreateFont(); headerFont.Color = NPOI.HSSF.Util.HSSFColor.White.Index; headerStyle.SetFont(headerFont); var headerRange = new NPOI.SS.Util.CellRangeAddress(rowNumber, rowNumber, 0, 6); accountSheet.AddMergedRegion(headerRange); var row = accountSheet.CreateRow(rowNumber); cell = row.CreateCell(0); cell.SetCellValue($"{monthName}"); cell.CellStyle = headerStyle; cell = row.CreateCell(6); cell.SetCellValue($"{monthName}"); cell.CellStyle = headerStyle; rowNumber++; foreach (var invoice in account.MonthlyDetails[monthIndex + 1].Invoices) { foreach (var invoiceAccount in invoice.AccountTotals) { row = accountSheet.GetRow(rowNumber) ?? accountSheet.CreateRow(rowNumber); cell = row.CreateCell(0); cell.SetCellValue(new AccountNumberTemplate(invoiceAccount.Account).stringifyAccountNumber()); cell.CellStyle = _borderStyle; cell = row.CreateCell(1); cell.SetCellValue(invoice.Vendor.Name); cell.CellStyle = _borderStyle; cell = row.CreateCell(2); cell.SetCellValue(invoice.InvoiceDate); cell.CellStyle = _dateStyle; cell = row.CreateCell(3); cell.SetCellValue(invoice.DatePaid); cell.CellStyle = _dateStyle; cell = row.CreateCell(4); cell.SetCellValue(invoice.InvoiceNumber); cell.CellStyle = _borderStyle; cell = row.CreateCell(5); cell.SetCellValue(invoice.Description); cell.CellStyle = _borderStyle; cell = row.CreateCell(6); cell.SetCellValue(System.Convert.ToDouble(invoiceAccount.Expense)); cell.CellStyle = _moneyStyle; rowNumber++; } } var usedRows = rowNumber - startRow; while (usedRows < minRows) { row = accountSheet.GetRow(rowNumber) ?? accountSheet.CreateRow(rowNumber); cell = row.CreateCell(0); cell.CellStyle = _borderStyle; cell = row.CreateCell(1); cell.CellStyle = _borderStyle; cell = row.CreateCell(2); cell.CellStyle = _dateStyle; cell = row.CreateCell(3); cell.CellStyle = _dateStyle; cell = row.CreateCell(4); cell.CellStyle = _borderStyle; cell = row.CreateCell(5); cell.CellStyle = _borderStyle; cell = row.CreateCell(6); cell.CellStyle = _moneyStyle; rowNumber++; usedRows++; } row = accountSheet.CreateRow(rowNumber); cell = row.CreateCell(0); cell.SetCellValue($"{monthName} Total"); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(1); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(2); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(3); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(4); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(5); cell.CellStyle = _monthlyTotalStyle; cell = row.CreateCell(6); cell.CellFormula = $"SUM(G{startRow+2}:G{rowNumber})"; cell.CellStyle = _monthlyTotalEndStyle; rowNumber++; return(rowNumber); }
private void WriteToExcel(DirectoryOperation operation) { try { string targetToOutput = ConfigurationManager.AppSettings["TargetToOutput"].ToString(); var workbook = new XSSFWorkbook(); if (File.Exists(targetToOutput)) { this.fileStream = new FileStream(targetToOutput, FileMode.Open /*, FileAccess.ReadWrite, FileShare.ReadWrite*/); workbook = new XSSFWorkbook(this.fileStream); this.fileStream.Close(); } else { this.fileStream = new FileStream(targetToOutput, FileMode.Create); this.fileStream.Close(); workbook = new XSSFWorkbook(); } //this.fileStream = new FileStream("C:\\Users\\GOOD\\Desktop\\test.xls", FileMode.Create, FileAccess.ReadWrite); ISheet sheet = null; bool notContain = true; if (workbook.NumberOfSheets != 0) { for (int i = 0; i < workbook.NumberOfSheets; i++) { if (workbook.GetSheetName(i).Equals($"{operation.Method.Name}")) { //workbook.RemoveSheetAt(i); notContain = false; sheet = workbook.GetSheet($"{operation.Method.Name}"); break; } else { notContain = true; } } } if (File.Exists(targetToOutput)) { this.fileStream = new FileStream(targetToOutput, FileMode.Truncate /*, FileAccess.ReadWrite*/); } if (notContain) { sheet = workbook.CreateSheet($"{operation.Method.Name}"); var row = sheet.CreateRow(0); var row2 = sheet.CreateRow(1); var row3 = sheet.CreateRow(2); var cellStyleBorder = workbook.CreateCellStyle(); cellStyleBorder.BorderBottom = BorderStyle.Thin; cellStyleBorder.BorderLeft = BorderStyle.Thin; cellStyleBorder.BorderRight = BorderStyle.Thin; cellStyleBorder.BorderTop = BorderStyle.Thin; cellStyleBorder.Alignment = HorizontalAlignment.Center; cellStyleBorder.VerticalAlignment = VerticalAlignment.Center; var cellStyleBorderAndColorGreen = workbook.CreateCellStyle(); cellStyleBorderAndColorGreen.CloneStyleFrom(cellStyleBorder); cellStyleBorderAndColorGreen.FillPattern = FillPattern.SolidForeground; ((XSSFCellStyle)cellStyleBorderAndColorGreen).SetFillForegroundColor(new XSSFColor(new byte[] { 198, 239, 206 })); var cellStyleBorderAndColorYellow = workbook.CreateCellStyle(); cellStyleBorderAndColorYellow.CloneStyleFrom(cellStyleBorder); cellStyleBorderAndColorYellow.FillPattern = FillPattern.SolidForeground; ((XSSFCellStyle)cellStyleBorderAndColorYellow).SetFillForegroundColor(new XSSFColor(new byte[] { 255, 235, 156 })); row.CreateCell(0); row.CreateCell(1); row2.CreateCell(0); row2.CreateCell(1); row3.CreateCell(0, CellType.String).SetCellValue("FullPath"); row3.Cells[0].CellStyle = cellStyleBorderAndColorYellow; row3.CreateCell(1, CellType.String).SetCellValue("FileName"); row3.Cells[1].CellStyle = cellStyleBorderAndColorYellow; var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 1); var cra2 = new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 1); sheet.AddMergedRegion(cra); sheet.AddMergedRegion(cra2); ICell cell = sheet.GetRow(0).GetCell(0); cell.SetCellType(CellType.String); cell.SetCellValue($"{operation.Method.Name}"); cell.CellStyle = cellStyleBorderAndColorGreen; sheet.GetRow(0).GetCell(1).CellStyle = cellStyleBorderAndColorGreen; ICell cell2 = sheet.GetRow(1).GetCell(0); cell2.SetCellType(CellType.String); cell2.SetCellValue($"Count files in ExpectSet {this.ShowerDerectoryInstance.SymmetricalDifference().Count}"); cell2.CellStyle = cellStyleBorderAndColorGreen; sheet.GetRow(1).GetCell(1).CellStyle = cellStyleBorderAndColorGreen; int x = 3; foreach (FileInfo info in this.ShowerDerectoryInstance.SymmetricalDifference()) { row = sheet.CreateRow(x); row.CreateCell(0).SetCellValue(info.FullName); row.CreateCell(1).SetCellValue(info.Name); x++; } } else { var row = sheet.GetRow(0) == null?sheet.CreateRow(0) : sheet.GetRow(0); var row2 = sheet.GetRow(1) == null?sheet.CreateRow(1) : sheet.GetRow(1); var row3 = sheet.GetRow(2) == null?sheet.CreateRow(2) : sheet.GetRow(2); var row3Cell0 = row3.GetCell(0) == null?row3.CreateCell(0) : row3.GetCell(0); var row3Cell1 = row3.GetCell(1) == null?row3.CreateCell(1) : row3.GetCell(1); row3Cell0.SetCellValue("FilePath"); row3Cell1.SetCellValue("FileName"); ICell cell = sheet.GetRow(0).GetCell(0); var row0Cell0 = row.GetCell(0) == null?row.CreateCell(0) : row.GetCell(0); row0Cell0.SetCellValue($"{operation.Method.Name}"); var row2Cell0 = row2.GetCell(0) == null?row2.CreateCell(0) : row2.GetCell(0); row2Cell0.SetCellValue($"Count files in ExpectSet {this.ShowerDerectoryInstance.SymmetricalDifference().Count}"); int x = 3; foreach (FileInfo info in this.ShowerDerectoryInstance.SymmetricalDifference()) { if (sheet.GetRow(x) == null) { row = sheet.CreateRow(x); } else { row = sheet.GetRow(x); } if (row.GetCell(0) != null) { row.GetCell(0).SetCellValue(info.FullName); } else { row.CreateCell(0).SetCellValue(info.FullName); } if (row.GetCell(1) != null) { row.GetCell(1).SetCellValue(info.Name); } else { row.CreateCell(1).SetCellValue(info.Name); } x++; } } sheet.AutoSizeColumn(0); sheet.AutoSizeColumn(1); workbook.Write(this.fileStream); } catch (ConfigurationErrorsException e) { throw e; } catch (NullReferenceException e) { throw e; } catch (ArgumentException e) { throw e; } catch (Exception e) { throw e; } finally { if (this.fileStream != null) { this.fileStream.Close(); } } }
private void generate_my_data() { ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw"); DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty); DataTable table = view.ToTable(); IRow row0 = sheet0.CreateRow(0); table.TableName = "Załatwienia"; table.Columns.Remove("id_"); row0.CreateCell(0).SetCellValue("Opis"); row0.CreateCell(1).SetCellValue("Sprawy według repetoriów i wykazów"); var crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet0.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 8); sheet0.AddMergedRegion(crs); row0 = sheet0.CreateRow(1); row0.CreateCell(1).SetCellValue("C"); row0.CreateCell(2).SetCellValue("CG-G"); row0.CreateCell(3).SetCellValue("Ns"); row0.CreateCell(4).SetCellValue("Nc"); row0.CreateCell(5).SetCellValue("Co"); row0.CreateCell(6).SetCellValue("Cps"); row0.CreateCell(7).SetCellValue("WSC"); row0.CreateCell(8).SetCellValue("Łącznie"); int rol = 2; foreach (DataRow rowik in table.Rows) { row0 = sheet0.CreateRow(rol); for (int i = 0; i < 9; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row0.CreateCell(i).SetCellValue(ji); row0.Cells[i].CellStyle = cellStyle; } catch (Exception) { row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach // druga tabela view = (DataView)statystyki.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table = view.ToTable(); table.TableName = "Załatwienia"; table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); //table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); // //robienie int ro = 2; //----------------- IDataFormat format = hssfworkbook.CreateDataFormat(); //----------------- ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia"); IRow row2 = sheet1.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet1.AddMergedRegion(crs); row2.CreateCell(1).SetCellValue("Nazwisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet1.AddMergedRegion(crs); row2.CreateCell(2).SetCellValue("Imię"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet1.AddMergedRegion(crs); row2.CreateCell(3).SetCellValue("Funkcja"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet1.AddMergedRegion(crs); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet1.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Liczba sesji"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 6); sheet1.AddMergedRegion(crs); row2.CreateCell(7).SetCellValue("Załatwienia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 7, 14); sheet1.AddMergedRegion(crs); row2.CreateCell(15).SetCellValue("Il. sporządzonych uzasadnień"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 15, 15); sheet1.AddMergedRegion(crs); row2.CreateCell(16).SetCellValue("Nieobecności"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 16, 17); sheet1.AddMergedRegion(crs); row2 = sheet1.CreateRow(1); row2.CreateCell(5).SetCellValue("rozprawy"); row2.CreateCell(6).SetCellValue("posiedzenia"); row2.CreateCell(7).SetCellValue("C"); row2.CreateCell(8).SetCellValue("C-GC"); row2.CreateCell(9).SetCellValue("Ns"); row2.CreateCell(10).SetCellValue("Nc"); row2.CreateCell(11).SetCellValue("Co"); row2.CreateCell(12).SetCellValue("Cps"); row2.CreateCell(13).SetCellValue("WSC"); row2.CreateCell(14).SetCellValue("Razem"); row2.CreateCell(16).SetCellValue("Urlopy"); row2.CreateCell(17).SetCellValue("Zwolnienia"); foreach (DataRow rowik in table.Rows) { row2 = sheet1.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // trzeci sheet view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); sheet1.AutoSizeColumn(0, true); sheet1.AutoSizeColumn(1, true); ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia"); row2 = sheet2.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet2.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Wyznaczenia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet2.AddMergedRegion(crs); row2 = sheet2.CreateRow(1); row2.CreateCell(5).SetCellValue("C"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("Ns"); row2.CreateCell(8).SetCellValue("Nc"); row2.CreateCell(9).SetCellValue("Co"); row2.CreateCell(10).SetCellValue("Cps"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet2.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // czwarty sheet view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); // table.Columns.Remove("id_tabeli"); // table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_09"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów"); row2 = sheet3.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet3.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet3.AddMergedRegion(crs); row2 = sheet3.CreateRow(1); row2.CreateCell(5).SetCellValue("C"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("Ns"); row2.CreateCell(8).SetCellValue("Nc"); row2.CreateCell(9).SetCellValue("Co"); row2.CreateCell(10).SetCellValue("Cps"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); // row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet3.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach }
private void btnsave_Click(object sender, EventArgs e) { try { path = Environment.CurrentDirectory.ToString() + @"\Excel\" + "\\"; IWorkbook workbook = new XSSFWorkbook(); //建立空白工作簿 ISheet sheet = workbook.CreateSheet("扫描数据"); //在工作簿中:建立空白工作表 IRow row = sheet.CreateRow(0); //在工作表中:建立行,参数为行号,从0计 ICell cell = row.CreateCell(0); //在行中:建立单元格,参数为列号,从0计 cell.SetCellValue("小型化激光雷达扫描数据"); //设置单元格内容 ICellStyle style = workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置单元格的样式:水平对齐居中 IFont font = workbook.CreateFont(); //新建一个字体样式对象 //font.Boldweight = short.MaxValue; //设置字体加粗样式 font.FontName = "宋体"; font.FontHeight = 16; style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中 cell.CellStyle = style; //将新的样式赋给单元格 row.Height = 30 * 20; //设置单元格的高度 //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15); //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域 sheet.AddMergedRegion(region); IRow row1 = sheet.CreateRow(1); 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("最小偏差"); row1.CreateCell(7).SetCellValue("平均值偏差"); row1.CreateCell(8).SetCellValue("离散度"); row1.CreateCell(9).SetCellValue(" "); row1.CreateCell(10).SetCellValue("绝对最大偏差"); row1.CreateCell(11).SetCellValue("绝对最小偏差"); row1.CreateCell(12).SetCellValue("绝对平均偏差"); row1.CreateCell(13).SetCellValue("偏移量"); row1.CreateCell(14).SetCellValue("绝对精度"); //设置单元格的宽度 for (int i = 0; i < 15; i++) { sheet.SetColumnWidth(i, 13 * 256);//设置单元格的宽度 //row1.GetCell(i).CellStyle = style;//设置单元格格式 } int len = Recordlist.Count; //int l_n32criticalno = 0; int temp = 0; if (len > 0) { //for (int i = 0; i < len; i++) //查找临界值 //{ // if (((RecordStruct)Recordlist[temp]).m_int32realdisc > 500 && // ((RecordStruct)Recordlist[temp]).m_int32realdisc < 1500) // { // l_n32criticalno = i; // break; // } //} for (int i = 0; i < len; i++) { IRow rowx = sheet.CreateRow(i + 2); //if (i % 2 == 0) //{ // temp = (i / 2 + 10); //temp = (i / 2 + l_n32criticalno+1); //} //else //{ // temp = (i - 1) / 2; //temp = (l_n32criticalno - (i - 1) / 2); //} temp = i; rowx.CreateCell(0).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32realdisc); rowx.CreateCell(1).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32max); rowx.CreateCell(2).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32min); rowx.CreateCell(3).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32ave); rowx.CreateCell(4); rowx.CreateCell(5); rowx.CreateCell(6); rowx.CreateCell(7); rowx.CreateCell(8); rowx.CreateCell(9); rowx.CreateCell(10); //绝对最大偏差 rowx.CreateCell(11); //绝对最小偏差 rowx.CreateCell(12); //绝对平均偏差 rowx.CreateCell(13); //偏移量 rowx.CreateCell(14); //绝对精度 rowx.CreateCell(15); //绝对精度 } //设置保留小数点的个数 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); IRow row2 = sheet.GetRow(2); ICell cellx13 = row2.GetCell(13); cellx13.SetCellFormula("AVERAGE(H3:H" + (len + 2).ToString() + ")"); //偏移量:平均偏差的平均值 cellx13.CellStyle = cellStyle; if (len > 0) { for (int i = 0; i < len; i++) { IRow rowx = sheet.GetRow(i + 2); string rowno = (i + 3).ToString(); ICell cellx5 = rowx.GetCell(5); cellx5.SetCellFormula("SUM(B" + rowno + ",A" + rowno + "*(-1))"); //最大偏差: 最大值减实际值 ICell cellx6 = rowx.GetCell(6); cellx6.SetCellFormula("SUM(C" + rowno + ",A" + rowno + "*(-1))"); //最小偏差:最小值减实际值 ICell cellx7 = rowx.GetCell(7); cellx7.SetCellFormula("SUM(D" + rowno + ",A" + rowno + "*(-1))"); //平均偏差: 平均值减实际值 ICell cellx8 = rowx.GetCell(8); cellx8.SetCellFormula("SUM(B" + rowno + ",C" + rowno + "*(-1))"); //离散度:最大值减最小值 ICell cellx10 = rowx.GetCell(10); cellx10.SetCellFormula("SUM(F" + rowno + ",N3*(-1))"); //绝对最大偏差:最大偏差减去偏移量 cellx10.CellStyle = cellStyle; ICell cellx11 = rowx.GetCell(11); cellx11.SetCellFormula("SUM(G" + rowno + ",N3*(-1))"); //绝对最小偏差:最小偏差减去偏移量 cellx11.CellStyle = cellStyle; ICell cellx12 = rowx.GetCell(12); cellx12.SetCellFormula("SUM(H" + rowno + ",N3*(-1))"); //绝对平均偏差:平均偏差减去偏移量 cellx12.CellStyle = cellStyle; } } //ICell cellx14 = row2.GetCell(14); //cellx14.SetCellFormula("SUM(MAX(F3:F"+(len+2).ToString()+"),MIN(G3:G"+(len+2).ToString()+")*(-1))"); //绝对精度:最大偏差的最大值减去最小偏差的最小值 IRow row3 = sheet.GetRow(3); ICell cell314 = row3.GetCell(14); cell314.SetCellValue("0.5-6m"); //0.5-6 ICell cell315 = row3.GetCell(15); cell315.SetCellFormula("SUM(MAX(F4:F14),MIN(G4:G14)*(-1))"); //0.5-6 绝对精度:最大偏差的最大值减去最小偏差的最小值 IRow row4 = sheet.GetRow(4); ICell cell414 = row4.GetCell(14); cell414.SetCellValue("0.5-8m"); //0.5-8 ICell cell415 = row4.GetCell(15); cell415.SetCellFormula("SUM(MAX(F4:F18),MIN(G4:G18)*(-1))"); //0.5-8绝对精度:最大偏差的最大值减去最小偏差的最小值 IRow row5 = sheet.GetRow(5); ICell cell514 = row5.GetCell(14); cell514.SetCellValue("0-10m"); //0.5-10 ICell cell515 = row5.GetCell(15); cell515.SetCellFormula("SUM(MAX(F3:F" + (len + 2).ToString() + "),MIN(G3:G" + (len + 2).ToString() + ")*(-1))"); //绝对精度:最大偏差的最大值减去最小偏差的最小值 if (ExcelFormName == null || ExcelFormName.IsDisposed) { ExcelFormName = new ExcelDocumentName(); } ExcelFormName.ShowDialog(this); ExcelName = ExcelFormName.name; if (ExcelName != null) { path = path + ExcelName + ".xlsx"; FileStream file2007 = new FileStream(path, FileMode.Create); workbook.Write(file2007); file2007.Close(); workbook.Close(); MessageBox.Show("数据保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); //加载excel System.Diagnostics.Process.Start(path); } } else { MessageBox.Show("记录数据为空,请重新记录!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch { MessageBox.Show("保存数据出错,请重新操作", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }