C# 处理 excel 合并计算
来源：互联网 发布：淘宝关键词搜索人气 编辑：程序博客网 时间：2024/05/22 14:42
最近遇到一个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 我。
0 0
- C# 处理 excel 合并计算
- C#处理多个Excel合并,复制Sheet
- C#处理多个Excel合并,复制Sh…
- C#合并Excel单元格
- Excel 建立合并计算【xpghost】
- C#实现Excel跨文件多SHEET合并计算（原创）
- C#读取EXCEL合并单元格
- C#中Excel单元格合并
- Excel合并计算和分类汇总
- C# 处理Excel 转
- c#处理excel表格
- C# 读取 Excel 中的合并单元格内容。
- C#读取Excel中合并单元格解决办法
- C#中操作Excel单元格合并
- C#用winform实现excel数据合并
- C# 获取Excel中的合并单元格
- C# Excel单元格合并及图片插入
- C# 获取Excel中的合并单元格
- fresco实现圆角、圆形头像
- 框架排错
- es5
- 在通话过程中，一直靠近距离传感器。通话结束时，要求马上点亮屏幕
- POJ 2482 Stars in Your Window (扫描线+线段树+离散化)
- C# 处理 excel 合并计算
- AJAX
- 总结：Linux体系结构和内核结构区别
- [BZOJ3168][Heoi2013]钙铁锌硒维生素（矩阵求逆+二分图匹配）
- 仿小红书顶部自适应高度viewpager
- es6
- Ｌｉｎｕｘ文件系统详解
- Android Studio 红米3 一直运行或者debug不成功，提示 Failed to establish session 解决方案
- 使用nexus3x部署管理maven私服
