C#Excel操作

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

这几天做一个小工具，需求是：1.服务器有一个Excel模板 2.用户通过配置文件来下载服务器上的Excel模板文件，并把配置文件内对模板指定位置插入数据。

根据需求我就上网对Excel操作的方式/方法，大概分为以下几种：

1.采用OleDB读取EXCEL文件：没研究（网络上说的）

优点：将Excel直接当做数据源处理，通过SQL直接读取内容，读取速度较快。

缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个Sheet页读取出来后（结果为Datatable）再在Datatable中根据行列数来获取指定的值。

当Excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。

2.引用微软提供的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件

代码：

        //读取EXCEL的方法
        private void SetExcel(string strFileName)
        {
            object missing = System.Reflection.Missing.Value;
            Application excel = new Application();
            string l_strSaveFileName = string.Empty;
            string l_strOperationSheet = string.Empty;
            string l_strOperationDB = string.Empty;
            Hashtable ht = new Hashtable();
            string l_strSavePath = string.Empty;

            excel.Visible = false;
            excel.UserControl = true;
            // 以只读的形式打开EXCEL文件
            Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
             missing, missing, missing, true, missing, missing, missing, missing, missing);
            //根据指定的sheet名称取得指定工作薄
            Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);

            try
            {
                //取得总记录行数   (包括标题列)
                int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
                //int colsint = ws.UsedRange.Cells.Columns.Count; //得到列数

                for (int i = 1; i < rowsint; i++)  //对工作表每一行  
                {
                    string cellValue = ws.UsedRange.Cells[i, 1].Text.ToString();
                    if (cellValue != "END")
                    {
                        if (cellValue == "ファイル名")
                        {
                            l_strSaveFileName = ws.UsedRange.Cells[i, 2].Text.ToString();
                        }
                        else if (cellValue == "シート名")
                        {
                            l_strOperationSheet = ws.UsedRange.Cells[i, 2].Text.ToString();
                        }
                        else if (cellValue == "対応付け")
                        {
                            ht[ws.UsedRange.Cells[i, 3].Text.ToString()] = ws.UsedRange.Cells[i, 2].Text.ToString();
                        }
                    }
                }

                //根据指定的sheet名称取得指定工作薄
                ws = (Worksheet)wb.Worksheets.get_Item(l_strOperationSheet);

                //设置指定区域内容 --C2,...
                foreach (string item in ht.Keys)
                {
                    ws.Cells.get_Range(item).Value = ht[item];
                }
                /*以下代码：主要是针对response下载文件而多做的，因为response无法将Workbook对象响应到客户端，所以我就用了以下方式：
                 *1.先把处理完成的Excel文件存放在服务器上的作为临时文件。
                 *2.把此临时文件转换为文件流
                 *3.Response.BinaryWrite方法把文件流响应到客户端。
                 *4.全部完成后，删除临时文件。
                 */
                //服务器临时文件
                l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);
                wb.Saved = true;
                wb.SaveCopyAs(l_strSavePath);

                using (FileStream fsRead = new FileStream(l_strSavePath, FileMode.Open))
                {
                    int fsLen = (int)fsRead.Length;
                    byte[] heByte = new byte[fsLen];
                    int r = fsRead.Read(heByte, 0, heByte.Length);

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment; filename=" + l_strSaveFileName);
                    Response.ContentType = "application/excel";
                    Response.ContentEncoding = Encoding.Default;
                    Response.BinaryWrite(heByte);
                    Response.End();
                }
            }
            finally
            {
                if (l_strSavePath != string.Empty && File.Exists(l_strSavePath))
                {
                    File.Delete(l_strSavePath);
                }
                wb.Close(false, missing, missing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
                wb = null;
                excel.Workbooks.Close();
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

优点：能够非常灵活的读取Excel中的数据，用户可以灵活的调用各种函数进行处理。

缺点：基于单元格的处理，读取速度较慢，对于数据量较大的文件最好不要使用此种方式读取。

需要添加相应的DLL引用，必须存在此引用才可使用，如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。
注：此方法可以处理已制定好的模板，如：

3.NPOI方式读取Excel

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。

优点：读取Excel速度较快，读取方式操作灵活性

缺点：需要下载相应的插件并添加到系统引用当中。并且会丢失模板的式样（如：在单元格内有值，单元格背景变色） --不适用于复杂模板

下载地址：http://npoi.codeplex.com/releases

        public void NPOIGetExcelDB(string p_strFileName)
        {
            StringBuilder l_strResult = new StringBuilder();
            IWorkbook workbook = null;  //新建IWorkbook对象  
            string l_strSaveFileName = string.Empty;
            string l_strOperationSheet = string.Empty;
            string l_strOperationDB = string.Empty;
            Hashtable ht = new Hashtable();
            //string fileName = "E:\\Excel2003.xls";
            FileStream fileStream = new FileStream(p_strFileName, FileMode.Open, FileAccess.Read);
            
            try
            {
                if (p_strFileName.IndexOf(".xlsx") > 0) // 2007版本  
                {
                    workbook = new XSSFWorkbook(fileStream);  //xlsx数据读入workbook  
                }
                else if (p_strFileName.IndexOf(".xls") > 0) // 2003版本  
                {
                    workbook = new HSSFWorkbook(fileStream);  //xls数据读入workbook  
                }
                ISheet sheet = workbook.GetSheetAt(0);  //获取第一个工作表  
                IRow row;// = sheet.GetRow(0);            //新建当前工作表行数据  
                for (int i = 0; i < sheet.LastRowNum; i++)  //对工作表每一行  
                {
                    row = sheet.GetRow(i);   //row读入第i行数据  
                    if (row != null)
                    {
                        string cellValue = row.GetCell(0).ToString(); //获取i行j列数据
                        if (cellValue != "END")
                        {
                            if (cellValue == "ファイル名")
                            {
                                l_strSaveFileName = row.GetCell(1).ToString();
                            }
                            else if (cellValue == "シート名")
                            {
                                l_strOperationSheet = row.GetCell(1).ToString();
                            }
                            else if (cellValue == "対応付け")
                            {
                                ht[row.GetCell(2).ToString()] = row.GetCell(1).ToString();
                            }
                        }
                    }
                }
                sheet = workbook.GetSheet(l_strOperationSheet);
                int rowindex = 0;
                int colindex = 0;
                //设置指定区域内容
                foreach (string item in ht.Keys)
                {
　　　　　　　　　　　  //1.NPOI无法处理Excel中列名（C2），所以做了转换
　　　　　　　　　　　　//并且Excel行列索引是从1开始的，而NPOI是从零开始的所以要减1.
                    rowindex = GetNumberic(item) - 1;
                    colindex = StringToNumber(GetStrings(item)) - 1;
                    if (sheet.LastRowNum >= rowindex)
                    {
                        row = sheet.GetRow(rowindex);
                    }
                    else
                    {
                        row = sheet.CreateRow(rowindex);
                    }

                    if (row.LastCellNum >= colindex)
                    {
                        row.GetCell(colindex).SetCellValue(ht[item].ToString());
                    }
                    else { row.CreateCell(colindex).SetCellValue(ht[item].ToString()); }
                }

                string l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);

                //转为字节数组
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(l_strSavePath, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            finally
            {
                fileStream.Close();
                workbook.Close();
            }
        }

转换方法：

        // Convert A, B, .... AAA, AAB to number 1,2, ...         
        int StringToNumber(string s)
        {
            int r = 0;
            for (int i = 0; i < s.Length; i++)
            {
                r = r * 26 + s[i] - 'A' + 1;
            }
            return r;
        }
　　　　 //获取数字
        int GetNumberic(string str)
        {

            Match ms = Regex.Match(str, @"\d+");

            return int.Parse(ms.Value);
        }
　　　　 //获取字母
        string GetStrings(string str)
        {
            Match ms = Regex.Match(str, @"[A-Z]+");

            return ms.Value;
        }

4.ClosedXML操作Excel（借鉴网络文章，如果对Excel模板处理，这个是非常好的）

获取页（Sheet）首先简单介绍两个类XLWorkbook和IXLWorksheet，分别对应着OpenXML里面的Workbook和Worksheet。XLWorkbook对应着你要访问的Excel文件，一个XLWorkbook会有一个IXLWorksheet集——IXLWorksheets，IXLWorksheet和Excel文件里面的Sheet是对应的。我们知道，一个Excel中可能会包含着很多个Sheet，XLWorkbook类中可以通过两种方式获取想要访问的页：

public IXLWorksheet Worksheet(int position);//根据索引
public IXLWorksheet Worksheet(string name);//根据sheet名称

第一种方式通过页的位置，也就是顺序来获取Sheet，一般第一页对应的参数为1；后一种通过页名来获取，具体使用哪一种当然要视情况而定。例如，需要遍历Excel文件中的所有内容时，第一种明显更好；如果是定位到某一页，第二种可能更恰当。单元格集合是在Sheet中的，所以要想访问数据，获取页是第一步。
读取数据范围当你用对象浏览器查看上文中IXLWorksheet类时，似乎觉得RowCount()和ColumnCount()这两个方法是来获取最大行数和列数的，但经过测试发现，这两个值往往都很大，包含了你没有存放数据的范围。后来查了一下类成员，发现了FirstRowUsed()这个函数，函数说明为“Gets the first row of the worksheet that contains a cell with a value”，紧接着就果然又发现了LastRowUsed()这个函数。简单测试了下，二者确实表示了包围所有数据的最大矩形的位置，所以包含数据的表范围是：

rowUsedCount = LastRowUsed().RowNumber() -FirstRowUsed().RowNumber()
ColumnUsedCount = LastColumnUsed().ColumnNumber() -FirstColumnUsed().ColumnNumber()

读取单元格数据读取单元格数据有两种方式：

IXLCell Cell(string cellAddressInRange);//通过单元格符号来读取，比如“A1”、“B1”之类的
IXLCell Cell(int row, int column);//通过行列的位置来读取如第三行第一列为Cell(3, 1)
IXLCell Cell(int row, string column);//就是二者的综合，因为在Excel里面，行是用数字表示的，而列则是用ABCD来表示的

第三种就是二者的综合，因为在Excel里面，行是用数字表示的，而列则是用ABCD来表示的。第二种更合适一些，更符合“表”的概念。

要使用ClosedXML处理Excel就要先引用ClosedXML.dll和DocumentFormat.OpenXML.dll程序集，这些程序集可是使用Visual Studio的NuGet一键安装（没用过，可以网上搜索）
针对上面所说的需求代码如下：

        //读取EXCEL的方法
        private void SetExcel(Stream strFileDB)
        {
            string l_strSaveFileName = string.Empty;
            string l_strOperationSheet = string.Empty;
            string l_strOperationDB = string.Empty;
            Hashtable ht = new Hashtable();
            string l_strSavePath = string.Empty;
            //实例一个Workbook 注意：XLWorkbook的参数如果我们传入的参数是文件流，发布以后不会包无访问权限，如果是文件路径的话必须把文件上传到服务器上才使用XLWorkbook
            var xlBook = new XLWorkbook(strFileDB);
            //实例一个worksheet  
            var ws = xlBook.Worksheet(1);

            try
            {
                //取得总记录行数   (包括标题列)
                int rowsint = ws.LastRowUsed().RowNumber() - ws.FirstRowUsed().RowNumber();

                for (int i = 1; i <= rowsint; i++)  //对工作表每一行  
                {
                    string cellValue = ws.Cell(i, 1).Value.ToString();
                    if (cellValue != "END")
                    {
                        if (cellValue == "ファイル名")
                        {
                            l_strSaveFileName = ws.Cell(i, 2).Value.ToString();
                        }
                        else if (cellValue == "シート名")
                        {
                            l_strOperationSheet = ws.Cell(i, 2).Value.ToString();
                        }
                        else if (cellValue == "対応付け")
                        {
                            ht[ws.Cell(i, 3).Value.ToString()] = ws.Cell(i, 2).Value.ToString();
                        }
                    }
                }

                //根据指定的sheet名称取得指定工作薄
                ws = xlBook.Worksheet(l_strOperationSheet);

                //设置指定区域内容
                foreach (string item in ht.Keys)
                {
                    ws.Cell(item).Value = ht[item];
                }
                //服务器临时文件
                l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);
                //xlBook.Saved = true;
                xlBook.SaveAs(l_strSavePath);

                using (FileStream fsRead = new FileStream(l_strSavePath, FileMode.Open))
                {
                    int fsLen = (int)fsRead.Length;
                    byte[] heByte = new byte[fsLen];
                    int r = fsRead.Read(heByte, 0, heByte.Length);

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment; filename=" + l_strSaveFileName);
                    Response.ContentType = "application/excel";
                    Response.ContentEncoding = Encoding.Default;
                    Response.BinaryWrite(heByte);
                    Response.End();
                }
            }
            finally
            {
                if (l_strSavePath != string.Empty && File.Exists(l_strSavePath))
                {
                    File.Delete(l_strSavePath);
                }
            }
        }

个人感觉最后一种方法处理Excel模板（有特殊样式的）是比较好的，但在国内的网上搜索的时候很不容易搜到ClosedXML操作Excel的方法。