Asp环境下的Excel导入导出技术浅析

1、A环境下的Excel导入导出技术浅析软件应用开发二部 张XX1 综述项目开发中经常会用到数据的导入导出，很多系统都留有Excel导入导出的功能，以便与自然人或其他系统交互数据。只要数据交互的双方都有Excel导入导出的接口，就能很方便地格式化处理对方的数据。一个优秀的系统必然有一些与其他系统的数据交互接口，Excel导入导出就是其中比较好的一个。系统留出Excel导入导出接口的一般做法是实现导入Excel、导出Excel和导出Excel模板这三种功能。在导入导出Excel的功能上可以加入一些查询限制条件，从而实现对数据的过滤。导出的Excel模板主要是方便其他系统直接使用，在Excel模板文件

2、中可以预设好固定的格式，包括字体颜色大小，合并单元格等。开发者可以通过系统程序生成Excel模板文件，对于丰富格式的模板最好通过Microsoft Excel软件设置并保存起来。 Microsoft Excel是优秀的软件产品，有着丰富的功能，对Excel文件进行编程操作也有多种方法。主要有Excel文件Sql连接读写、Excel Com组件读写、字符流Excel文件读写三种方式。A技术有着大量的B/S应用，具有很好的易用性和丰富的功能。本文使用A的C#后台语言，结合有着较完美功能和界面的Ext前台JS脚本技术进行Excel文件的读写操作和导入导出演示说明。本文将分别从Excel导入和导出两个

3、方面分析几种可能的方法，对方法应用中可能遇到的问题也视情况进行探讨。最后比较各个方法的优缺点，总结使用的方式和注意事项等。网络上已经有了大量的关于Asp.Net Excel导入导出的资料，在参考这些资料的基础上本文侧重实践应用中遇到的一些问题的解决和汇总分析。2 Excel导入 导入主要有两种方式Excel文件Sql连接读取和Excel Com组件读取。把Excel文件保存成CSV文件（列之间用英文逗号分隔，行之间用换行符分隔）或者TXT文件（列之间用制表符分隔，行之间用换行符分隔），然后再使用文本流的方式读取也可以作为一种方法。无论是哪种方式，Excel文件都需要把文件放在导入程序所在的机器

4、本地磁盘上。对于Asp.Net网站就是要把Excel数据文件上传到后台网站服务器后才能执行导入。2.1 Excel文件Sql连接读取使用Sql连接Excel文件的方式读取数据时是把Excel文件当做数据库来操作，把Excel工作表当做数据表，Excel的列当做数据表的字段。下面是连接Excel文件导入数据的示例代码：public DataSet InputExcel() DataSet ds=null; string strCon = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =D:test.xls; +Extended Properti

5、es= Excel 8.0;HDR=YES;IMEX=1; OleDbConnection conn = new OleDbConnection(strCon); try string sql = select * from Sheet1$; conn.Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter(sql, strCon); ds = new DataSet(); myAdapter.Fill(ds, Sheet1$); catch finally conn.Close(); return ds;上例是从“D:test.xl

6、s”Excel文件中读取名称为“sheet1”的工作表中的内容到DataSet中。导出数据到DataSet后就可以在系统内部进行格式化处理等操作了。“HDR=YES”表示第一行作为标题，这也是默认值，如果第一行数据有空着的地方，查询到DataSet中的列名会使用诸如“F2”，“F8”等来代替。“IMEX=1”表示以只读方式读取。扩展属性Extended Properties= Excel 8.0 是必须的，如果不这样指定,微软Jet数据库引擎（JET是Joint Engine Technology 的简称）会把连接到的文件默认为是Access数据库文件。“Sheet1$”表示Excel文件中的

7、工作表名称sheet1，在查询语句中要使用“”+工作表名+“$”格式指定工作表，也可以使用“”+工作表名+“$”的格式。中括号和“(键盘左上方下面的字符)”用来工作表名称，Microsoft 建议使用方括号，这是用于表示未知数据库对象名的标准约定。JET数据库引擎程序需要用到“msjetoledb40.dll”，根据版本不同动态库文件也有所不同。此动态库一般在安装Office时会注册安装，如果没有的话需要单独下载并使用“regsvr32 msjetoledb40.dll”命令行注册。上例中的连接字符串对Excel 97-2003有效。如果要连接的是Excel2007文件，连接字符串要使用“Pr

8、ovider=Microsoft.ACE.OLEDB.12.0;Data Source=c:myFoldermyExcel2007file.xlsx;Extended Properties=Excel 12.0 Xml;HDR=YES;”。如果要查询工作表sheet中的某几列的内容，可以在select 后面指定列名，多个列名之间以英文逗号分开。其他更复杂的查询与一般的数据库查询语句类似，如可以加上条件判断等，在此不做赘述。底层的数据连接引擎还可以使用ODBC驱动程序的提供程序，其连接字符串如下面所示：“Provider = MSDASQL;Driver=Microsoft Excel Driv

9、er (*.xls); DBQ=C: test.xls; ReadOnly=False;”相应的Connection和 DataAdapter都要改成System.Data.Odbc命名空间下的类。在查询的时候，如果Excel文件里面有合并的单元格，驱动程序会取合并区域的内容作为最左上角单元格的内容。这与我们在Excel文件中手动合并单元格时Excel应用程序告诉我们只会保留最左上角的单元格的内容相似。Excel文件中合并的单元格区域只是显示方式不同，内部还是有着区块的划分。下面的无论是COM组件读取还是文本流读取也都是这样默认的。2.2 Excel Com组件读取这里说的Excel Com组

10、件是指“Excel Object Library COM组件”，VisualStudio2008里面可以通过添加对“Microsoft.Office.Interop.Excel.dll”的引用来操作Excel文件。调用Com组件操作Excel文件需要设置调用权限，Asp.Net后台运行时一般使用的是Asp.Net账户，需要在后台服务器上赋予它调用Excel的DCOM权限。下面的导出Excel使用COM组件时也需要相同的设置。设置方法如下：a在服务器上安装office的Excel软件.b在开始-运行中输入dcomcnfg.exe启动组件服务c依次双击组件服务-计算机-我的电脑-DCOM配置d在D

11、COM配置中找到Microsoft Excel 应用程序,在它上面点击右键,然后点击属性,弹出Microsoft Excel 应用程序属性对话框e点击标识标签,选择交互式用户f点击安全标签,在启动和激活权限上点击自定义,然后点击对应的编辑按钮,在弹出的安全性对话框中填加一个NETWORK SERVICE用户(注意要选择本计算机名),并给它赋予本地启动和本地激活权限.g依然是安全标签,在访问权限上点击自定义,然后点击编辑,在弹出的安全性对话框中填加一个NETWORK SERVICE用户,然后赋予本地访问权限.这样,我们便配置好了相应的Excel的DCOM权限.注意:这是在WIN2003上配置的,

12、在2000上,可能是配置ASPNET用户。 在WindowsXp下经过测试，权限设置应用后后台账户即能调用Excel的DCOM功能，不需要重启系统或者Web服务器。在Windows2003服务器上需要重启系统才能应用。 在A环境下调用Excel COM组件后，最容易出现的问题就是非托管资源的回收，如果处理不好而又频繁使用很可能造成服务器的崩溃。.net回收非托管资源（比如COM资源）可以使用Marshal类(负责在托管对象和非托管对象之间复制内存和转换)的ReleaseComObject方法，通过减少对COM资源对象引用计数的方法让COM对象在内存中消失。创建Excel Com对象、打开Exc

13、el文件和释放COM资源的方法如下面所示：public class ExcelHandlerprivate Missing miss = Missing.Value; /忽略的参数OLENULL public static Missing MissValue = Missing.Value; private Microsoft.Office.Interop.Excel.Application m_objExcel;/Microsoft.Office.Interop.Excel应用程序实例 private Microsoft.Office.Interop.Excel.Workbooks m_ob

14、jBooks;/工作表集合 private Microsoft.Office.Interop.Excel.Workbook m_objBook;/当前操作的工作表 private Microsoft.Office.Interop.Excel.Worksheet sheet;/当前操作的表格 / / 打开Excel文件,实例化COM对象 / / public void OpenExcelFile(string filename) m_objExcel = new Microsoft.Office.Interop.Excel.Application(); UserControl(false); m

15、_objExcel.Workbooks.Open( filename,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBook = m_objExcel.ActiveWorkbook; sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet; / /

16、释放Excel COM资源 / public void ReleaseExcel() try m_objExcel.Quit(); int refCom= Marshal.ReleaseComObject(m_objExcel); Marshal.ReleaseComObject(m_objBooks); Marshal.ReleaseComObject(m_objBook); Marshal.ReleaseComObject(sheet); GC.Collect(); catch / public class ExcelHandler注意最后的ReleaseExcel方法释放COM资源的过程

17、，这个方法要在确认不再使用这些COM对象的时候调用。最好的确保释放资源的方法是搜寻名称为“Excel”的进程并分别使用Process.kill()方法“杀”掉进程，这种做法可能误杀，还有就是账户不会具有这么大的权限，因此一般不会使用。下面是COM读取Excel文件的示例，主要使用工作表的get_Range方法，获取范围区块，然后赋值到DataTable中。/ / 读取Excel文件的某几列，返回DataTable / / public DataTable COMRead() DataTable dt = new DataTable(); ExcelHander eh = new ExcelHa

18、nder(); try eh.OpenExcelFile(E:ProjectsWebTesta.xls);/设置当前的表单sheet页 为 第1个sheet页，也可通过名称指定，如果不设置的话会默认为最后一次活动的sheet页/当Excel文件有多个sheet页，导入需要在多个sheet页之间切换时采用 /如果需要返回多个sheet页的导入内容，应为每个sheet页实例化一个DataTable对象，最后以DataSet的形式返回 eh.CurrentSheet = eh.CurrentWorkBook.Worksheets1 as Microsoft.Office.Interop.Excel.

19、Worksheet; int rowsCount = eh.CurrentSheet.UsedRange.Cells.Rows.Count; /得到行数，从而读取一列的所有行 /读取Excel当前工作表中的A列，从A1开始表示第一行的标题内容也读取 Microsoft.Office.Interop.Excel.Range raA = eh.CurrentSheet.get_Range(A1, A + rowsCount.ToString();/只读一个单元格 /只读一个单元格 Microsoft.Office.Interop.Excel.Range raA1 = eh.CurrentSheet

20、.get_Range(A1,Type.Missing); string a1Value = (string)raA1.Value2;/此时Value2就只是一个string /读取A列，B列，C列 Microsoft.Office.Interop.Excel.Range raABC = eh.CurrentSheet.get_Range(A1, C+rowsCount.ToString(); object, objsA = (object,)raA.Value2; /Value2属性表示读取到的值，如果读的是多个单元格则是一个二维Object数组 /第一维表示序列，不管是从sheet页的第几行

21、开始读结果都是从1开始计数 /第二维是1列序，也是从1开始，如果是读取单列则一直是1 object, objsABC = (object,)raABC.Value2; /自己构造数据列 dt.Columns.Add(objsABC1,1=null)?A:objsABC1,1.ToString(), typeof(string); dt.Columns.Add(objsABC1, 2 = null) ? B : objsABC1, 2.ToString(), typeof(string); dt.Columns.Add(objsABC1, 3 = null) ? C : objsABC1, 3.

22、ToString(), typeof(string); for (int i = 2; i = rowsCount - 1; i+) dt.Rows.Add(new object objsABCi, 1, objsABCi, 2, objsABCi, 3 ); catch (Exception ee) Response.Write(ee.Message); finally eh.ReleaseExcel(); return dt;这种方法读取比较好的地方就是可以随意读取某个区块或单元格，不好的地方是无法添加选择条件过滤数据，读出来的数据也需要做进一步的处理才能形成DataTable等。2.3

23、文本流读取Excel转换的CSV或TXT文件 所谓“CSV”，是Comma Separated Value（逗号分隔值）的英文缩写，通常都是纯文本文件。 下面是一个实际CSV文件中的部分内容：目录代号,分类号,分类名称,案卷号,档号,编制单位*,立卷单位700201,8603,施工设计,2,700201-2,安徽送变电工程公司700201,8603,施工设计,3,700201-3,安徽送变电工程公司700201,8603,施工设计,4,700201-4,安徽送变电工程公司通常CSV文件开头是不留空的，以行为单位，每行中记录多项数据，每项数据用英文逗号来分隔。这种方法需要先把待导入的Excel文

24、件另存为CSV格式然后再上传导入。下面是导入的示例代码：/ / 文本流读取 / / public DataTable TextStreamRead() DataTable dt = new DataTable(); FileStream fs = null; StreamReader sr = null; try fs = new FileStream(E:ProjectsWebTesttest.csv, FileMode.Open, FileAccess.Read, FileShare.None); / fs = new FileStream(E:ProjectsWebTesttest.tx

25、t, FileMode.Open, FileAccess.Read, FileShare.None); sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(GB2312); string strS = null; string strLine = sr.ReadLine();/每次读取一行作为一条记录 if (strLine = null) throw new Exception(空文件！); strS = strLine.Split(,); /strS = strLine.Split(t); /如果是制表符+换行符分开的txt

26、格式Excel文件，则以t制表符分隔字符串获取数据项 for (int i = 0; i strS.Length; i+) /第一行标题作为列名 dt.Columns.Add(strSi = null | strSi = ) ? (Column + i.ToString() : strSi, typeof(string); strLine = sr.ReadLine(); while (strLine != null) strS = strLine.Split(,); /strS = strLine.Split(t); dt.Rows.Add(strS); strLine = sr.ReadL

27、ine(); catch finally sr.Close(); fs.Close(); return dt; / public DataTable TextStreamRead()如上面代码中说明的那样，这种方法也适用于“制表符+换行符分开的txt格式Excel文件”。无论是将Excel文件保存成CSV格式还是TXT格式，都会有一个不好的地方是只能用于单个工作表的Excel文件，下面的导出方法中的文本流导出也是这样。3 Excel导出Excel的导出也主要是上述三种方法的逆向操作，把从系统中获取的DataTable或DataSet中的数据写入Excel文件中。后台导出的Excel文件可以先存

28、放在服务器网站某个目录下面然后等待前台获取下载，或者直接将Excel文件以数据流的方式导出至前台，需求而定。方法说明中许多要点已经在上一章节“Excel导入”部分作了说明，这部分主要展示下具体的代码示例，对需要重点说明的“题外”要点做些讨论。3.1 Excel文件Sql连接写入Excel文件的Sql连接的注意要点在Excel导入部分已经做了很多说明。连接到Excel文件后，主要是采用Sql Insert语句向Excel文件中插入数据。/ / 向Excel文件写入数据 / public void SqlWrite() DataTable dt = FillData(); string strCo

29、n = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =E:ProjectsWebTesta.xls ;Extended Properties=Excel 8.0;HDR=yes;imex=0; /string strCon = Provider = MSDASQL;Driver=Microsoft Excel Driver (*.xls); DBQ=E:ProjectsWebTesta.xls; ReadOnly=False; /如果使用ODBC驱动，则要将连接字符串，CONN和CMD都换成ODBC格式的即可 OleDbConnection

30、 conn = new OleDbConnection(strCon); /OdbcConnection conn = new OdbcConnection(strCon); try string sql = ; conn.Open(); OleDbCommand myCommand = new OleDbCommand(, conn); /OdbcCommand myCommand = new OdbcCommand(, conn); for (int i = 0; i dt.Rows.Count; i+) sql = string.Format( insert into Sheet1$ (

31、A,Bbb,Cccc,D) values(0,1,2,3), dt.RowsiColumnA,/获取要插入的对应的数据 dt.RowsiColumnB, dt.RowsiColumnC, dt.RowsiColumnD ); myCommand.CommandText = sql; if (myCommand.ExecuteNonQuery() != 1) throw new Exception(插入数据异常！); catch(Exception ee) throw ee; finally conn.Close(); / public void SqlWrite()需要注意的地方是连接字符串中

32、的IMEX要设置为0，才能写入文件。如果使用ODBC连接操作Excel文件，使用注释中的代码即可。通过Sql连接向Excel文件写入数据要确保写入每列的数据格式和Excel文件中的各列的数据格式对应一致。如果导出的Excel文件没有对单元格内容数据格式的特殊要求，可以将Excel模板文件中的所有单元格都设置成“文本”格式，然后在插入的Sql语句中对应的也都以字符串的格式导入即可。3.2 Excel Com组件写入COM组件写入Excel文件，可以一个单元格一个单元格的写入，更多的是通过区域块的设置。向区域块Range赋值时需要先将待导入的DataTable中的数据构造成二维数组，然后再向区域块

33、对象的“Value2”属性赋值。通过WorkSheet对象的get_Range方法获取区域块时要注意，行号从2开始表示第一行为标题行，列号从1开始。区域块的终点单元格的选中要根据待导入的DataTable的行数和列数获取，这样才能获取待输入数据的整个工作区。如果Datatable的行数太多，可能会使生成的二维值数组太大，此时可以分批次选择工作区域块Range，将Datatable中的行数也对应的分批。COM组件操作时还可以设置Excel文件的格式，如字体、颜色等。 / / 使用COM组件 向Excel文件写入数据 / private void COMWrite() ExcelHander eh

34、 = new ExcelHander(); try DataTable dt = FillData(); eh.OpenExcelFile(E:ProjectsWebTesta.xls); /一个单元格写入 /eh.CurrentSheet.Cells6, 5 = 测试; /区域块设置 Microsoft.Office.Interop.Excel.Range ra = eh.CurrentSheet.get_Range(eh.CurrentSheet.Cells2,1, eh.CurrentSheet.Cellsdt.Rows.Count+1,dt.Columns.Count); string

35、, valueStr = new stringdt.Rows.Count,dt.Columns.Count; for (int i = 0; i dt.Rows.Count; i+) for (int j = 0; j dt.Columns.Count; j+) valueStri, j = (dt.Rowsij = null) ? : dt.Rowsij.ToString(); ra.Value2 = valueStr;/赋值 ra.Font.Underline = true;/下划线 ra.Font.Color = Color.Green.ToArgb();/字体颜色设置，必须使用ToAr

36、gb的格式 ra.Font.Bold = true;/粗体 ra.Interior.ColorIndex = 39;/背景色索引 string fileName = E:ProjectsWebTestaabb.xls; if (File.Exists(fileName) File.Delete(fileName); eh.SaveAs(fileName); catch (Exception ee) Response.Write(ee.Message); finally eh.ReleaseExcel(); /private void COMWrite()3.3 文本流输出CSV或TXT的类似E

37、xcel文件文本流输出的其实不是真正的Excel文件，将其保存成Excel文件的后缀名是为了指定使用Excel程序打开。这种导出方法主要是向输出流中写入格式化的文本字符，在流的另一端接收完成后保存起来。详见下面的示例。/ / 文本流输出 / public void TextStreamWrite() Response.ContentEncoding = System.Text.Encoding.GetEncoding(GB2312); Response.AppendHeader(Content-Disposition, attachment;filename=test.xls); DataTa

38、ble dt = FillData(); DataRow myRow = dt.Select(); string colHeaders = string.Empty; int i = 0; /取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for (i = 0; i dt.Columns.Count - 1; i+) colHeaders += dt.Columnsi.Caption.ToString() + t; colHeaders += dt.Columnsi.Caption.ToString() + n; /向HTTP输出流中写入取得的数据信息 Response.Wr

39、ite(colHeaders); /逐行处理数据 string lineStr = string.Empty; foreach (DataRow row in myRow) /在当前行中，逐列获得数据，数据之间以t分割，结束时加回车符n for (i = 0; i row.ItemArray.Length - 1; i+) lineStr += rowi.ToString() + t; lineStr += rowi.ToString() + n; /当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 Response.Write(lineStr); lineStr = ; /写

40、缓冲区中的数据到HTTP头文件中 Response.End();/ public void TextStreamWrite()这种方式只能导出单个工作表的类似Excel文件。如果数据是在Web页面的控件上，比如DataGrid等，可以使用控件的RenderControl方法将控件的内容渲染到文本操作流上从而输出Excel文件。这其实也是文本流输出Excel的方法，只是输出的数据源不同。详见下例：/ / 页面控件输出Excel / / public void DGToExcel(System.Web.UI.Control ctl) HttpContext.Current.Response.App

41、endHeader(Content-Disposition, attachment;filename=Excel.xls); HttpContext.Current.Response.Charset = UTF-8; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = application/ms-excel; ctl.Page.EnableViewState = false; System.IO.Strin

42、gWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString(); HttpContext.Current.Response.End();/DGToExcel(System.Web.UI.Control ctl)4 一个Excel导出实例通过Excel COM组件导入或者导出Excel文件时

43、都会遇到效率的问题，操作速度可能会比较慢，在导入导出的数据量比较大的时候会更明显。通过区域块取值和赋值速度上要比单个单元格的操作要快很多，但还是需要一定的时间。采用Sql连接或者文本流的方式操作Excel文件时也会遇到这样的问题。后台导出的时间过长就会影响界面响应速度，不能给用户很好的操作体验。所以最好能在后台导出时前台也能及时将导出的进度信息显示出来。下面探讨一种已在实际项目中应用的A环境下的导入导出信息即时显示解决方案。在安徽中兴继远信息技术开发工程建设施工现场档案资料同步管理系统（简称PDMIS）中，需要将系统内的档案卷宗目录等信息导出到一份Excel模板文件中，导出完成后再将该文件导入另外一套系统中，从而实现了PDMIS的对外数据输出接口。下面简要介绍Excel导出的流程和进度信息即时显示的实现方法。处理流程说明 1、前台点击导出按钮时，引发TestState请求。2、后台返回当前的Excel导出状态，如果是WaitRequest状态则告诉前台可以开始检查导