using NPOI.SS.UserModel; using NPOI.SS.UserModel.Charts; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using OTSCommon.DBOperate.Model; using OTSIncAReportApp.DataOperation.DataAccess; using OTSIncAReportMailInterface; using System; using System.Data; using System.Drawing; using System.IO; using System.Linq; namespace OTSIncAReportGrids { public class ExcelExport { private System.Windows.Forms.DataGridView dgV_ParticlesDevidePage; DataTable particlesFilter; ParticleData Particledata; Frm_UserProgress frm_userprogress; public ExcelExport(System.Windows.Forms.DataGridView a_dgV_ParticlesDevidePage, DataTable a_particlesFilter, ParticleData a_particlesData, Frm_UserProgress a_frm_userprogress) { dgV_ParticlesDevidePage = a_dgV_ParticlesDevidePage; particlesFilter= a_particlesFilter; Particledata = a_particlesData; frm_userprogress = a_frm_userprogress; } public void ExportToExcel(OTSIncAReportApp._1_UI.Control_Grids.SpectrumExportConfirmation sfd) { IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Particles"); ICellStyle cellStyle = CreateDefaultCellStyle(workbook); //设置颗粒名列宽 sheet.SetColumnWidth(1, 30 * 256); //夹杂物名列宽 sheet.SetColumnWidth(2, 7 * 256); //图像列宽 //插入表头 IRow headerRow = sheet.CreateRow(1); headerRow.Height = 30 * 20; for (int i = 0; i < dgV_ParticlesDevidePage.Columns.Count; i++) { ICell cell = headerRow.CreateCell(i); cell.CellStyle = cellStyle; cell.SetCellValue(dgV_ParticlesDevidePage.Columns[i].HeaderText); } frm_userprogress?.SetProgressValueAndText(0, $"export: 0/total: {dgV_ParticlesDevidePage.Rows.Count}"); ExportParticleData(workbook, sheet, cellStyle, sfd); ExportSpectrumData(workbook, cellStyle, sfd); ExportSpectrumConfirmation(workbook, cellStyle, sfd); //完成后，对Excel进行保存 using (FileStream file = new FileStream(sfd.FileName, FileMode.Create)) { workbook.Write(file); } frm_userprogress?.SetProgressValueAndText(100, $"export: {dgV_ParticlesDevidePage.Rows.Count}/total: {dgV_ParticlesDevidePage.Rows.Count}"); } private ICellStyle CreateDefaultCellStyle(IWorkbook workbook) { ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //设置边框 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //设置字体 IFont font = workbook.CreateFont(); font.FontName = "黑体"; font.FontHeightInPoints = 10; cellStyle.SetFont(font); return cellStyle; } private void ExportParticleData(IWorkbook workbook, ISheet sheet, ICellStyle cellStyle, OTSIncAReportApp._1_UI.Control_Grids.SpectrumExportConfirmation sfd) { IDrawing patriarch = sheet.CreateDrawingPatriarch(); for (int i = 0; i < dgV_ParticlesDevidePage.Rows.Count; i++) { UpdateExportProgress(i, dgV_ParticlesDevidePage.Rows.Count); IRow row = sheet.CreateRow(2 + i); row.Height = 45 * 20; for (int j = 0; j < dgV_ParticlesDevidePage.Columns.Count; j++) { ExportCellData(workbook, sheet, patriarch, row, cellStyle, i, j, sfd); } } } private void UpdateExportProgress(int current, int total) { if (total > 100 && current % (total / 100) == 0) { frm_userprogress?.SetProgressValueAndText( current * 100 / total, $"export: {current}/total: {total}"); } } private void ExportCellData(IWorkbook workbook, ISheet sheet, IDrawing patriarch, IRow row, ICellStyle cellStyle, int rowIndex, int colIndex, OTSIncAReportApp._1_UI.Control_Grids.SpectrumExportConfirmation sfd) { ICell cell = row.CreateCell(colIndex); cell.CellStyle = cellStyle; if (dgV_ParticlesDevidePage[colIndex, rowIndex].Value == null) return; if (colIndex == 2 && sfd.IfExportPicture) //图像列 { ExportImageCell(workbook, sheet, patriarch, cell, rowIndex, colIndex); } else //非图像列 { ExportDataCell(cell, rowIndex, colIndex); } } private void ExportImageCell(IWorkbook workbook, ISheet sheet, IDrawing patriarch, ICell cell, int rowIndex, int colIndex) { if (dgV_ParticlesDevidePage.Rows[rowIndex].Cells[colIndex].Value is Bitmap bp) { using (MemoryStream ms = new MemoryStream()) { bp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] bytes = ms.ToArray(); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); IClientAnchor anchor = patriarch.CreateAnchor(1, 1, 2, 2, colIndex, rowIndex + 2, colIndex + 1, rowIndex + 3); patriarch.CreatePicture(anchor, pictureIdx); } } } private void ExportDataCell(ICell cell, int rowIndex, int colIndex) { var cellValue = dgV_ParticlesDevidePage[colIndex, rowIndex].Value; if (double.TryParse(cellValue.ToString(), out double dval)) { cell.SetCellType(CellType.Numeric); cell.SetCellValue(dval); } else { cell.SetCellValue(cellValue.ToString()); } } private void ExportSpectrumData(IWorkbook workbook, ICellStyle cellStyle, OTSIncAReportApp._1_UI.Control_Grids.SpectrumExportConfirmation sfd) { if (!sfd.IfExportSpectrumData) return; ISheet sheet = workbook.CreateSheet("Peak spectrum Data"); IRow headerRow = sheet.CreateRow(1); headerRow.Height = 30 * 20; ICell firstCell = headerRow.CreateCell(0); firstCell.CellStyle = cellStyle; firstCell.SetCellValue("rowid"); for (int i = 1; i < 2001; i++) { ICell cell = headerRow.CreateCell(i); cell.CellStyle = cellStyle; if (i == 1) { cell.SetCellValue("PeakSpectrumData"); } } sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2000)); for (int j = 0; j < particlesFilter?.Rows.Count; j++) { ExportSpectrumDataRow(sheet, cellStyle, j); } } private void ExportSpectrumDataRow(ISheet sheet, ICellStyle cellStyle, int rowIndex) { IRow row = sheet.CreateRow(2 + rowIndex); ICell cell = row.CreateCell(0); cell.CellStyle = cellStyle; cell.SetCellValue(rowIndex + 1); var tmpPart = Particledata.GetParticleXrayDataByFidAndPid( particlesFilter.Rows[rowIndex]["fieldid"].ToString(), particlesFilter.Rows[rowIndex]["particleid"].ToString()); if (tmpPart?.XrayId > -1) { ExportSpectrumValues(row, cellStyle, tmpPart); } } private void ExportSpectrumValues(IRow row, ICellStyle cellStyle, Particle tmpPart) { uint[] analysisXray = new uint[2000]; for (int i = 0; i < 2000; i++) { analysisXray[i] = BitConverter.ToUInt32(tmpPart.XRayData, i * 4); ICell cell = row.CreateCell(i + 1); cell.CellStyle = cellStyle; cell.SetCellValue(analysisXray[i]); } } private void ExportSpectrumConfirmation(IWorkbook workbook, ICellStyle cellStyle, OTSIncAReportApp._1_UI.Control_Grids.SpectrumExportConfirmation sfd) { if (!sfd.IfExportSpectrumConfirmation) return; ISheet sheet = workbook.CreateSheet("Peak spectrum"); sheet.SetColumnWidth(1, 200 * 256); //图像列宽 IRow headerRow = sheet.CreateRow(1); headerRow.Height = 30 * 20; ICell firstCell = headerRow.CreateCell(0); firstCell.CellStyle = cellStyle; firstCell.SetCellValue("rowid"); ICell secondCell = headerRow.CreateCell(1); secondCell.CellStyle = cellStyle; secondCell.SetCellValue("PeakSpectrum"); for (int j = 0; j < particlesFilter?.Rows.Count; j++) { ExportSpectrumChart(sheet, cellStyle, j); } } private void ExportSpectrumChart(ISheet sheet, ICellStyle cellStyle, int rowIndex) { IRow row = sheet.CreateRow(rowIndex + 2); row.Height = 120 * 20; ICell cell = row.CreateCell(0); cell.CellStyle = cellStyle; cell.SetCellValue(rowIndex + 1); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 1, rowIndex + 2, 2, rowIndex + 3); CreateChart(drawing, sheet, anchor, rowIndex + 2); } private void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, int rowid) { if (!(drawing.CreateChart(anchor) is XSSFChart chart)) return; var data = chart.ChartDataFactory.CreateLineChartData(); IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; leftAxis.IsVisible = true; bottomAxis.IsVisible = true; IChartDataSource ys = DataSources.FromNumericCellRange( sheet, new CellRangeAddress(rowid, rowid, 1, 2001)); double[] doubles = Enumerable.Range(0, 2000).Select(i => (double)i).ToArray(); IChartDataSource xs = DataSources.FromArray(doubles); data.AddSeries(xs, ys); } } }