OTS/OTSIncAReportApp/1-UI/Control_Grids/ParticlesGridDevidePage/ExcelExport.cs

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<double, double>();
            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<double> ys = DataSources.FromNumericCellRange(
                sheet, new CellRangeAddress(rowid, rowid, 1, 2001));

            double[] doubles = Enumerable.Range(0, 2000).Select(i => (double)i).ToArray();
            IChartDataSource<double> xs = DataSources.FromArray(doubles);
            data.AddSeries(xs, ys);
        }
    }
}