C# 处理 excel 合并计算

最新推荐文章于 2024-01-21 11:07:42 发布

九星上将

最新推荐文章于 2024-01-21 11:07:42 发布

阅读量815

点赞数

文章标签： excel 合并 c#

本文链接：https://blog.csdn.net/qq_32477537/article/details/68946200

版权

最近遇到一个Excel合并计算的需求：

1、管理人员上传Excel模版

2、管理人员选择模版发送给选择人员进行填写

3、接受人员填写后返回给管理人员

4、管理人员选择模版进行汇总

5、多个Excel合并到一个Excel

6、合并后的Excel需要将每个sheet对应位置的值汇总

7、汇总分为两种：计算、合并

计算：计算对应位置的值的和

合并：将相同位置的行汇总到一起

同时保持原来的计算公式。

刚拿到这个需求时，感觉很简单，没什么问题，但在实际开发中却遇到很多问题：

1、Excel兼容性问题

2、填写人员发送给管理员的问题，如何保证传的就是管理员要的

3、Excel模版没有标准，计算和合并是混合使用的

4、计算时原有计算公式不能丢失

额，和废话有点多，直接上我怎么实现的吧！

首先使用了 DSOFramer 和 NPOI

DSOFramer是为了可在线编辑Excel，NPOI是为了合并和计算，Excel都以二进制存在数据库中

一、将Excel的二进制放到本地Excel

private string LoadFile()
        {
            try
            {
                string systemPath = System.Windows.Forms.Application.StartupPath;
                string tempPath = systemPath + "\\modeltmp";
                ServiceReference.ExcelWebServersSoapClient server = new ServiceReference.ExcelWebServersSoapClient();
                if (lx == 0 || lx == 1)
                {
                    DataTable dt = server.Query("select FileName,FileContext from SendReport where FaCode='" + App.FaCode + "'");
                    if (dt.Rows.Count > 0)
                    {
                        //获取二进制文件
                        byte[] fileContext = (byte[])dt.Rows[0]["FileContext"];
                        //获取文件名称
                        string filename = dt.Rows[0]["FileName"].ToString();
                        App.FileName = filename;
                        //检测文件夹是否存在,不存在就创建
                        FileIO.CreateFileDirectory(tempPath);
                        //清空文件夹下的文件
                        FileIO.ClearDownloadDirectory(tempPath);
                        //拼接保存地址
                        string savepath = tempPath + "\\" + filename;
                        //将二进制文件存为本地excel文件
                        FileBinaryConvertHelper.Bytes2File(fileContext, savepath);
                        return savepath;
                    }
                }
                //if (lx == 1)
                //{
                //    sendReportModelList =
                //        sqlConnection.Query<SendReportModel>(
                //            "select FileName,UpDateContext from SendReport where FaCode=@facode ",
                //            new { facode = App.FaCode }).ToList();
                //    if (sendReportModelList.Count > 0)
                //    {
                //        //获取二进制文件
                //        byte[] fileContext = sendReportModelList[0].UpDateContext;
                //        //获取文件名称
                //        string filename = sendReportModelList[0].FileName;
                //        App.FileName = filename;
                //        //检测文件夹是否存在,不存在就创建
                //        FileIO.CreateFileDirectory(tempPath);
                //        //清空文件夹下的文件
                //        FileIO.ClearDownloadDirectory(tempPath);
                //        //拼接保存地址
                //        string savepath = tempPath + "\\" + filename;
                //        //将二进制文件存为本地excel文件
                //        FileBinaryConvertHelper.Bytes2File(fileContext, savepath);
                //        return savepath;
                //    }
                //}
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

二、用DOSFramer打开

private void TianXie_Load(object sender, EventArgs e)
        {
            try
            {
                //获取二进制文件并转换成文件保存到程序根目录下，并打开
                string path = LoadFile();
                if (axFramerControl2 != null)
                {
                    string fullpath = System.IO.Path.GetFullPath(path);
                    //axFramerControl1.ShowView(3);
                    axFramerControl2.Open(fullpath, true, "Excel.Sheet", "", ""); //打开文件
                    App.isOpen = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                GC.Collect();
            }

        }

三、编辑好后保存

if (App.isOpen)
            {
                try
                {
                    //axFramerControl1.ShowDialog(DSOFramer.dsoShowDialogType.dsoDialogSave);
                    string systemPath = System.Windows.Forms.Application.StartupPath;
                    string tempPath = systemPath + "\\savetemp";
                    //检测文件夹是否存在,不存在就创建
                    FileIO.CreateFileDirectory(tempPath);
                    //清空文件夹下的文件
                    FileIO.ClearDownloadDirectory(tempPath);
                    object savepath = tempPath + "\\" + App.FileName;
                    object fileformat = 18;
                    axFramerControl2.SaveAs(savepath, dwFileFormat: 18);
                    MessageBox.Show("保存成功！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }


            }

四、保存完成后保存到数据库

DialogResult dr = MessageBox.Show("确定您已保存了修改内容？", "确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            if (dr == DialogResult.OK)
            {
                try
                {
                    //首先关闭excel文档
                    axFramerControl2.Close();
                    string systemPath = System.Windows.Forms.Application.StartupPath;
                    string tempPath = systemPath + "\\savetemp";
                    string savepath = tempPath + "\\" + App.FileName;
                    //文件转成byte二进制数组
                    byte[] byteArray = FileBinaryConvertHelper.File2Bytes(savepath);
                    string str = Convert.ToBase64String(byteArray);
                    ServiceReference.ExcelWebServersSoapClient server = new ServiceReference.ExcelWebServersSoapClient();

                    int n = server.ExcuteByte("update SendReport set UpDateContext=@updatecontext where FaCode='" + App.FaCode + "'", str, "@updatecontext");
                    if (n > 0)
                    {
                        MessageBox.Show("上传成功！");
                        this.Close();
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

五、设置计算合并规则

左侧是模版，列表是这个模版的所有规则，下方是设置规则

六、合并和计算

private void HeBing()
        {
            try
            {
                //第一步，获取模版的数据，并保存到本地
                BindingManagerBase bmb = this.BindingContext[this.dataModel.DataSource, this.dataModel.DataMember];
                DataRow row = ((DataRowView)bmb.Current).Row;
                string systemPath = System.Windows.Forms.Application.StartupPath;
                string filecode = row["filecode"].ToString();
                string tempPath = systemPath + "\\hbmodeltemp";
                string tempPath2 = systemPath + "\\hbmodeltemp2";
                //检测文件夹是否存在,不存在就创建
                FileIO.CreateFileDirectory(tempPath);
                FileIO.CreateFileDirectory(tempPath2);
                //清空文件夹下的所有文件
                FileIO.ClearDownloadDirectory(tempPath);
                FileIO.ClearDownloadDirectory(tempPath2);
                //将模版加载到本地
                string mPath = GetFile(filecode, "hbmodeltemp");
                //获取所有模版填写过的文档，并加载到本地，名字以用户加文件名的形式做文件名
                List<string> zPathList = new List<string>();
                //Dictionary<int,string> zPathDict=new Dictionary<string, string>();
                ServiceReference.ExcelWebServersSoapClient server = new ServiceReference.ExcelWebServersSoapClient();
                DataTable dt = server.Query("select FileName,SendUserName,UpDateContext from dbo.SendReport where FileCode='" + filecode + "' and UpDateContext is not null");

                foreach (DataRow m in dt.Rows)
                {
                    //获取二进制文件
                    byte[] fileContext = (byte[])m["UpDateContext"];
                    //获取文件名称
                    string filename = m["SendUserName"].ToString() + "-" + m["FileName"].ToString();
                    //拼接保存地址
                    string savepath = tempPath + "\\" + filename;
                    //将二进制文件存为本地excel文件
                    FileBinaryConvertHelper.Bytes2File(fileContext, savepath);
                    //创建数据流将文件的sheet名称修改成唯一名称
                    FileStream fsR = new FileStream(savepath, FileMode.Open, FileAccess.ReadWrite);
                    POIFSFileSystem f = new POIFSFileSystem(fsR);
                    HSSFWorkbook workbook = new HSSFWorkbook(f);
                    //获取sheet的个数
                    int sheetNumber = workbook.NumberOfSheets;
                    for (int i = 0; i < sheetNumber; i++)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        string oldName = sheet.SheetName;
                        workbook.SetSheetName(i, m["SendUserName"].ToString() + "_" + oldName);
                    }
                    FileStream fsW = new FileStream(savepath, FileMode.Create, FileAccess.ReadWrite);
                    workbook.Write(fsW);
                    fsW.Close();
                    fsR.Close();
                    zPathList.Add(savepath);
                }

                //合并
                //打开模版文件并将sheet复制到模版中
                FileStream modelFsR = new FileStream(mPath, FileMode.Open, FileAccess.ReadWrite);
                HSSFWorkbook workbookModel = new HSSFWorkbook(modelFsR);
                int sheetNuber = workbookModel.NumberOfSheets;
                for (int j = 0; j < zPathList.Count; j++)
                {
                    FileStream zFsR = new FileStream(zPathList[j], FileMode.Open, FileAccess.ReadWrite);
                    HSSFWorkbook workbookChildren = new HSSFWorkbook(zFsR);
                    int sheetNumber = workbookChildren.NumberOfSheets;
                    for (int i = 0; i < sheetNumber; i++)
                    {
                        HSSFSheet sheettemp1 = workbookChildren.GetSheetAt(i) as HSSFSheet;
                        sheettemp1.CopyTo(workbookModel, sheettemp1.SheetName, true, true);
                    }
                    zFsR.Close();
                }
                FileStream mfsW = new FileStream(mPath, FileMode.Create, FileAccess.ReadWrite);
                workbookModel.Write(mfsW);
                mfsW.Close();
                modelFsR.Close();
                JiSuan(mPath, filecode, sheetNuber);

                MessageBox.Show("合并完成");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                GC.Collect();
            }

        }

        private void JiSuan(string path, string filecode, int sheetmodleNumber)
        {
            FileStream fsR = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
            POIFSFileSystem f = new POIFSFileSystem(fsR);
            HSSFWorkbook workbook = new HSSFWorkbook(f);
            //获取sheet的总个数
            int sheetNumber = workbook.NumberOfSheets;
            //获取计算和合并的配置
            ServiceReference.ExcelWebServersSoapClient server = new ServiceReference.ExcelWebServersSoapClient();
            DataTable dt = server.Query("select Sheet,FangShi,StarRow,StarColum,EndRow,EndColum from SheZhi where filecode='" + filecode + "' order by sheet asc ");


            if (dt.Rows.Count > 0)
            {
                foreach (DataRow sz in dt.Rows)
                {
                    int sheetId = (int)sz["Sheet"] - 1;
                    int starrow = (int)sz["StarRow"] - 1;
                    int starcolum = (int)sz["StarColum"] - 1;
                    int endrow = (int)sz["EndRow"] - 1;
                    int endcolum = (int)sz["EndColum"] - 1;
                    string fs = sz["FangShi"].ToString();
                    ISheet sheettemp = workbook.GetSheetAt(sheetId);
                    if (fs == "计算")
                    {
                        for (int i = starrow; i <= endrow; i++)
                        {
                            for (int j = starcolum; j <= endcolum; j++)
                            {
                                IRow row = sheettemp.GetRow(i);
                                ICell cell = row.GetCell(j);
                                int num = sheetId + sheetmodleNumber;
                                double value = 0.00;

                                while (num <= sheetNumber)
                                {
                                    ISheet sheetOther = workbook.GetSheetAt(num);
                                    IRow rowOther = sheetOther.GetRow(i);
                                    ICell cellOther = rowOther.GetCell(j);
                                    switch (cellOther.CellType)
                                    {
                                        case CellType.Formula:
                                            cell.CellFormula = cellOther.CellFormula;
                                            break;
                                        case CellType.Numeric:
                                            value += cellOther.NumericCellValue;
                                            cell.SetCellValue(value);
                                            break;
                                        case CellType.String:
                                            break;
                                        case CellType.Unknown:
                                            break;
                                    }
                                    num += sheetmodleNumber;
                                }

                            }
                        }
                    }
                    if (fs == "合并")
                    {
                        int num = sheetId + sheetmodleNumber;
                        int cx = 0;
                        while (num <= sheetNumber)
                        {
                            if (cx != 0)
                            {
                                InsertRow(sheettemp, starrow + (endrow - starrow) * cx, starrow + (endrow - starrow) * cx + 1, (endrow - starrow) + 1);
                                //InsertRow(sheettemp, starrow-1 , endrow-1, endrow - starrow);
                            }
                            ISheet sheetOther = workbook.GetSheetAt(num);
                            int fromrow = starrow;
                            for (int i = starrow + (endrow - starrow) * cx + cx; i <= endrow + (endrow - starrow) * cx + cx; i++)
                            {
                                for (int j = starcolum; j <= endcolum; j++)
                                {
                                    CopyRange(sheetOther, sheettemp, fromrow, j, i, j, false, false);
                                }
                                fromrow++;
                            }
                            num += sheetmodleNumber;
                            cx++;

                            //MyInsertRow(sheettemp, endrow + (endrow - starrow)*cx, endrow - starrow, sheettemp.GetRow(endrow + (endrow - starrow)*cx));
                        }
                    }

                }

                FileStream mfsW = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
                string address = txtAddrees.Text;
                FileIO.CreateFileDirectory(address);
                FileIO.ClearDownloadDirectory(address);
                FileStream mfsW1 = new FileStream(address+"\\"+"Excel计算后文件.xls", FileMode.Create, FileAccess.ReadWrite);
                workbook.Write(mfsW);
                workbook.Write(mfsW1);
                mfsW.Close();
                mfsW1.Close();
                fsR.Close();
            }
        }
        private void InsertRow(ISheet sheet, int starRow, int endRow, int rows)
        {
            /*
             * ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeight); 
             * 
             * startRow 开始行
             * endRow 结束行
             * n 移动行数
             * copyRowHeight 复制的行是否高度在移
             * resetOriginalRowHeight 是否设置为默认的原始行的高度
             * 
             */

            sheet.ShiftRows(starRow + 1, endRow + 1, rows, true, true);

            starRow = starRow - 1;

            for (int i = 0; i < rows; i++)
            {

                IRow sourceRow = null;
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;

                short m;

                starRow = starRow + 1;
                sourceRow = sheet.GetRow(starRow);
                targetRow = sheet.CreateRow(starRow + 1);
                targetRow.HeightInPoints = sourceRow.HeightInPoints;

                for (m = (short)sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
                {

                    sourceCell = sourceRow.GetCell(m);
                    targetCell = targetRow.CreateCell(m);
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);
                }
            }

        }
        private void CopyRange(ISheet fromSheet, ISheet toSheet, int fromRowIndex, int fromColIndex, int toRowIndex, int toColIndex, bool onlyData, bool copyComment)
        {
            IRow sourceRow = fromSheet.GetRow(fromRowIndex);
            ICell sourceCell = sourceRow.GetCell(fromColIndex);
            //HSSFRow sourceRow = myHSSFWorkBook.GetSheetAt(myHSSFWorkBook.ActiveSheetIndex).GetRow(fromRowIndex);
            //HSSFCell sourceCell = sourceRow.GetCell(fromColIndex);
            if (sourceRow != null && sourceCell != null)
            {
                IRow changingRow = null;
                ICell changingCell = null;
                changingRow = toSheet.GetRow(toRowIndex);
                if (changingRow == null)
                    changingRow = toSheet.CreateRow(toRowIndex);
                changingCell = changingRow.GetCell(toColIndex);
                if (changingCell == null)
                    changingCell = changingRow.CreateCell(toColIndex);

                if (onlyData)//仅数据
                {
                    //对单元格的值赋值
                    changingCell.SetCellValue(sourceCell.StringCellValue);
                }
                else         //非仅数据
                {
                    //changingCell.CellStyle = sourceCell.CellStyle;
                    switch (sourceCell.CellType)
                    {
                        case CellType.Formula:
                            changingCell.CellFormula = changingCell.CellFormula;
                            break;
                        case CellType.Numeric:
                            changingCell.SetCellValue(sourceCell.NumericCellValue);
                            break;
                        case CellType.String:
                            changingCell.SetCellValue(sourceCell.StringCellValue);
                            break;
                        case CellType.Unknown:
                            changingCell.SetCellValue(sourceCell.StringCellValue);
                            break;
                    }
                }
            }
        }

最关键的就是第六步了，至此，功能就完成了。如果有不明白的可以QQ229685423 我。