excel单元格加公式

-_0

于 2022-09-18 11:39:47 发布

阅读量203

点赞数

分类专栏： C# 文章标签： c# NOPI

本文链接：https://blog.csdn.net/u014170719/article/details/126915628

版权

C# 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

今天弄了个给现有的excel文件加公式的功能。

protected void Unnamed_Click(object sender, EventArgs e)
        {
            HttpPostedFile cfile = Request.Files[0];
            string cfileName = System.IO.Path.GetFileName(cfile.FileName.ToString());
            string cfileNameEx = System.IO.Path.GetExtension(cfileName);

            string str_gui = Guid.NewGuid().ToString();
            string nowdate = DateTime.Now.ToString("yyyymmdd");
            string filename = "GG" + nowdate + "_" + str_gui + cfileNameEx;
            string _save_path = "D:\\GG\\" + filename;
            FileUpload1.SaveAs(_save_path);

            IWorkbook workbook;
            using (FileStream fs = File.Open(_save_path, FileMode.Open, FileAccess.Read))
            {
                if (cfileNameEx.ToUpper() == ".XLSX")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else {
                    workbook = new HSSFWorkbook(fs);
                }
                
            }
            ISheet sheet = workbook.GetSheetAt(0);
            IRow row;
            ICell cell;
            int lastnum = sheet.LastRowNum;
            int rowindex = 0;
            int cellindex = 0, pre_arrayindex = 0;
            //int rowhead = 1;
            int rowseq = 2, rowseq2 = 0, rowseq3 = 0;
            string[] cellname = new string[] { "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO" };
            string gscell = "";
            int celllength = cellname.Length;
            while (rowindex < lastnum)
            {
                rowindex = rowindex + 3;
                row = sheet.GetRow(rowindex);
                rowseq2 = rowseq + 1;
                rowseq3 = rowseq + 2;
                for (int arrayindex = 0; arrayindex < celllength; arrayindex++)
                {
                    cellindex = arrayindex + 11;
                    
                    pre_arrayindex = arrayindex - 1;
                    if (arrayindex == 0)
                    {
                        gscell = "I" + rowseq3.ToString() + "+J" + rowseq3.ToString() + "-L" + rowseq.ToString();
                    }
                    else {
                        gscell = cellname[pre_arrayindex] + rowseq2.ToString() + "+" + cellname[pre_arrayindex] + rowseq3.ToString() + "-" + cellname[arrayindex] + rowseq.ToString();
                    }
                    
                    cell = row.GetCell(cellindex);
                    cell.CellFormula = gscell;
                }
                rowseq = rowseq + 3;
            }
            using (FileStream fs2 = File.Open(_save_path, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs2);
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
                Response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Flush();
                ms.Position = 0;
            }
            File.Delete(_save_path);
        }