关于ASP.Net的导入导出（5）

最新推荐文章于 2025-09-06 22:56:13 发布

yuxiaoyu.

最新推荐文章于 2025-09-06 22:56:13 发布

阅读量126

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 runtime

原文链接：http://www.cnblogs.com/freewiller/archive/2006/09/10/500326.html

本文介绍了一个用于操作Excel文件的帮助类ExcelHelper，支持两种访问方式：通过Excel COM组件和通过ADO.NET将Excel视为数据库。提供了创建表格、读写数据等功能。

Com.urp.command.FileFrameWork（5）

好久没写了，呵呵，一个多月了，不知道有没关心这个系列的朋友，如果有，只能说声对不起了。主要是项目比较忙，再加上最近一直在玩三国志11的游戏，又浪费不少时间，于是晚上结束后也没多少时间来做自己的东西了。有时候想想，明明是国人自己的文化，却要玩有别人来做成的游戏，岂不也是一种悲哀，而且据说在中国，三国志已经被光荣注册成功了商标，所以国人以后开发的三国系列的游戏的命名都会是一种困难了吧。最近这次有差不多做了2周吧，改动比较大，除了把对于DataTable的支持加进去以后，重要是整个DataAdapter包结构的变化比较大,且听我一一道来：）

首先要说一下自己写的一个帮助类ExcelHelper。可以用两种方式访问Excel文件，一种是以Excel的Com组件的形式，一种是最简单的把Excel看作是数据库。其实很多的内容网上都已经有了，但都比较分散，都只提到了一种或两种，没有统一到一起。代码如下：

using System;

using System.IO;

using System.Collections;

using System.Text;

using System.Data;

using System.Data.OleDb;

namespace com.urp.command.FileFrameWork.Helper

{

/**//// <summary>

/// Class1 的摘要说明。

/// </summary>

public class ExcelHelper

{

private readonly string readConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source={0};"

+"Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";

private readonly string writeConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source={0};"

+"Extended Properties='Excel 8.0;HDR=YES;'";

private OleDbConnection _conn = null;

private OleDbCommand _command = null;

private OleDbDataAdapter _adapter = null;

private OleDbCommandBuilder _builder = null;

private Excel.Application _application = null;

private Excel._Workbook _workBook = null;

private Excel._Worksheet _workSheet = null;

private Excel.Range _range = null;

private int _columnCount;

private int _rowCount;

private object _missingValue = System.Reflection.Missing.Value;

public ExcelHelper(string path)

{

readConn = String.Format(readConn, path);

writeConn = String.Format(writeConn, path);

}

public ExcelHelper()

{

}

ComExcel#region ComExcel

public int UsedColumnCount

{

get

{

if(_columnCount == 0)

_columnCount = _workSheet.UsedRange.Columns.Count;

return _columnCount;

}

public int UsedRowCount

{

get

{

if(_rowCount == 0)

_rowCount = _workSheet.UsedRange.Rows.Count;

return _rowCount;

}

//打开一个Excel线程

public void OpenWorkBook(string path)

{

try

{

_application = new Excel.ApplicationClass();

_application.Visible = false;

_workBook = _application.Workbooks.Open(path, 0, true, 5, "", "", false,

Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

}

catch(Exception ex)

{

throw ex;

}

//新建一个Excel线程

public void NewWorkBook()

{

_application = new Excel.ApplicationClass();

_application.Visible = false;

_workBook = (Excel._Workbook)(_application.Workbooks.Add(_missingValue));

}

//根据名字获取sheet

public bool OpenWorkSheet(string sheetName)

{

_workSheet = (Excel._Worksheet)_workBook.Worksheets.get_Item(sheetName);

if(_workSheet != null)

return true;

return false;

}

//默认获得第一个sheet

public bool OpenWorkSheet(int sheetIndex)

{

_workSheet = (Excel._Worksheet)_workBook.Worksheets.get_Item(sheetIndex + 1);

if(_workSheet != null)

return true;

return false;

}

//保存

public void SaveSheet(string path)

{

_workBook.SaveAs(path, _missingValue, _missingValue, _missingValue, _missingValue,

_missingValue, Excel.XlSaveAsAccessMode.xlNoChange, _missingValue, _missingValue,

_missingValue, _missingValue, _missingValue);

}

//保存并关闭

public void CloseSaveSheet(string path)

{

// Remove(path);

this.SaveSheet(path);

this.CloseSheet();

}

//关闭

public void CloseSheet()

{

if(_workBook != null)

_workBook.Close(false, _missingValue, _missingValue);

if(_application != null)

_application.Quit();

if(_range != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(_range);

_range = null;

}

if(_workSheet != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(_workSheet);

_workSheet = null;

}

if(_workBook != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(_workBook);

_workBook = null;

}

if(_application != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(_application);

_application = null;

}

GC.Collect();

}

CellByCell#region CellByCell

public object GetCellByCell(int rowIndex, int columnIndex)

{

_range = _workSheet.get_Range(CellStr(rowIndex, columnIndex), _missingValue);

return _range.Value2;

}

public void SetCellByCell(int rowIndex, int columnIndex, object value)

{

_range = _workSheet.get_Range(CellStr(rowIndex, columnIndex), _missingValue);

_range.set_Value(_missingValue, value);

}

//自动将行数加一

private string CellStr(int rowIndex, int columnIndex)

{

return (char)(65 + columnIndex) + (++rowIndex).ToString();

}

#endregion

range#region range

public Array GetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd)

{

_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));

return (Array)_range.Value2;

}

public void SetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd, Array values)

{

_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));

_range.Value2 = values;

}

#endregion

ADO.Net#region ADO.Net

//以非导入的方式打开连接

public void OpenReadConnection()

{

_conn = new OleDbConnection(readConn);

_conn.Open();

}

//以非导入的方式打开连接

public void OpenWriteConnection()

{

_conn = new OleDbConnection(writeConn);

_conn.Open();

}

//在excel中先建一个sheet并写入列名

public void NewTable(DataTable dataTable)

{

StringBuilder columnNames = new StringBuilder("Create Table ");

columnNames.Append("sheet1(");

foreach(DataColumn column in dataTable.Columns)

{

columnNames.Append(column.ColumnName);

columnNames.Append(" varchar, ");

}

columnNames.Replace(", ", ")", columnNames.Length - 2 , 2);

Command.CommandText = columnNames.ToString();

Command.ExecuteNonQuery();

}

//关闭连接

public void CloseConnection()

{

if(_conn != null)

_conn.Close();

_conn = null;

_command = null;

_adapter = null;

_builder = null;

}

DataReader#region DataReader

public IDataReader GetByDataReader()

{

Command.CommandText = "Select * From [sheet1$]";

return Command.ExecuteReader();

}

public IDataReader GetByDataReader(int rowStart, int columnStart, int rowEnd, int columnEnd)

{

string range = CellStr(rowStart, columnStart) + ":" + CellStr(rowEnd, columnEnd);

Command.CommandText = "Select * From [sheet1$" + range + "]";

return Command.ExecuteReader();

}

#endregion

DataTable#region DataTable

public DataTable GetByDataTable()

{

Command.CommandText = "select * from [sheet1$]";

DataTable dt = new DataTable();

Builder = new OleDbCommandBuilder(Adapter);

Adapter.Fill(dt);

return dt;

}

public void SetByDataTable(DataTable dataTable)

{

try

{

Command.CommandText = "select * from [sheet1$]";

DataTable dt = new DataTable("sheet1");

Builder = new OleDbCommandBuilder(Adapter);

Adapter.Fill(dt);

Adapter.InsertCommand = Builder.GetInsertCommand();

Adapter.Update(dataTable);

dt = null;

}

catch(OleDbException ex)

{

throw ex;

}

private OleDbCommand Command

{

get

{

if(_command == null)

{

_command = new OleDbCommand();

_command.Connection = _conn;

}

return _command;

}

private OleDbDataAdapter Adapter

{

get

{

if(_adapter == null)

{

_adapter = new OleDbDataAdapter();

_adapter.SelectCommand = Command;

}

return _adapter;

}

private OleDbCommandBuilder Builder

{

get

{

return _builder;

}

set

{

_builder = value;

_builder.QuotePrefix="["; //获取insert语句中保留字符（起始位置）

_builder.QuoteSuffix="]"; //获取insert语句中保留字符（结束位置）

}

#endregion

}

如果把Excel看作数据库，那么要注意连接字符串：

private readonly string readConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source={0};"

+"Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";

HDR=YES 表示将sheet的第一行作为列名，所以我们默认excel的首行是列名。

IMEX=1 表示大致的意思是使用导入的模式，把数字也作为字符串来操作。

有一点很重要。IMEX=1，是一种导入的模式，所以首先这个文件要存在，如果不存在会报错：“Microsoft Jet 数据库引擎找不到对象'…\Customer.xls'。请确定对象是否存在，并正确地写出它的名称和路径”，而且这样写了以后就算文件是存在的，还有一个问题是不能对文件更新的，会提示“不能修改表 'sheet1' 的设计。它在只读数据库中”等错误，甚至还有提示权限的问题。

字符串的问题解决了以后，剩下的就是象数据库一样来操作了。如读excel只需要然后返回一个DataTable，其实这里应该可以返回后可以关闭连接，但是因为把数据库的打开已经提出去了做了一个单独的方法，所以把关闭也放到了外面，由导入，结束后再调用关闭的函数，而且这样做在形式上也和导出保持了一致。到目前位置还没发现这样做的问题。

public DataTable GetByDataTable()

{

Command.CommandText = "select * from [sheet1$]";

DataTable dt = new DataTable();

Builder = new OleDbCommandBuilder(Adapter);

Adapter.Fill(dt);

return dt;

}

至于导出，我们希望也是用DataTable来完成，网上有很多是一行一行插入的，想必在性能上肯定不如用Iadapter的方法Update来的快吧。而且单独写Insert方法的话还要处理参数的问题，要写插入语句，形式上烦了好多。所以我们利用DataTable的自动更新，用OleDbCommandBuilder来产生InserCommand。但是利用这种方法的问题是要求先要有表结构，所以我们要有一个产生表结构的方法，来写入列名，然后用一个select来填充一个空的table，同时来生成InsertCommand，然后更新的时候却是更新我们传入的dataTable，代码如下：

public void SetByDataTable(DataTable dataTable)

{

Command.CommandText = "select * from [sheet1$]";

DataTable dt = new DataTable("sheet1");

Builder = new OleDbCommandBuilder(Adapter);

Adapter.Fill(dt);

Adapter.InsertCommand = Builder.GetInsertCommand();

Adapter.Update(dataTable);

dt = null;

}

如何产生新的sheet的代码如下，这里还有一个问题，excel文件还不存在，怎么可以新建sheet了呢，是否还需要新建excel呢？我们的回答是不需要，当你的连接字符串里没有IMEX=1的，连接的同时如果没有文件，会自动新建，这一点省了好多。然后只要用“create table 表名(列名类型)”，类型全部用varchar安全又简单。

public void NewTable(DataTable dataTable)

{

StringBuilder columnNames = new StringBuilder("Create Table ");

columnNames.Append("sheet1(");

foreach(DataColumn column in dataTable.Columns)

{

columnNames.Append(column.ColumnName);

columnNames.Append(" varchar, ");

}

columnNames.Replace(", ", ")", columnNames.Length - 2 , 2);

Command.CommandText = columnNames.ToString();

Command.ExecuteNonQuery();

}

ExcelHelper中还有返回IdataReader对象的方法，本来以为要用来写入列名的，发现后来已经用不到了。至于用Com的方法来访问就复杂了好多了。其实用这种方法来访问，灵活性会大好多，字体，格式，等等都可以操作。具体的例子网上也后好多，可以看一下这篇的文章http://support.microsoft.com/default.aspx?scid=kb;zh-cn;306023

这里我只说一下我自己的考虑，使用“自动化功能将数据数组传输到工作表上的区域”，我采用的是这片文章的第二中方法，至于格式之类的都不错处理，因为毕竟不是做报表，只是导入导出而已。用COM的方法访问excel最关键是是Range做为访问对象，这个Range可以是一个Cell，可以是一行，一列，也可以是一个sheet，所以如果我们能一次就把sheet读入内存，然后再来处理，避免反复的读文件，提高性能，事实证明确实是这样，在某些情况下，甚至超过了ADO.Net来访问的性能。文章中有行代码就是我采用这种方法的关键，“m_objRange.Value = objData;”这个objData是一个2维的数组，我们平时用的比较多的是object[], object[,]这种形式的数组，其实数组的类是Array，为了访问和转化的方便，我采用了Array的形式，不熟的朋友可以去查一下，里面有很多方法我们需要用到，在这里就不具体介绍了。代码如下，都是用Array来作为参数和返回值的。

public Array GetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd)

{

_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));

return (Array)_range.Value2;

}

public void SetByRangeArray2(int rowStart, int columnStart, int rowEnd, int columnEnd, Array values)

{

_range = _workSheet.get_Range(CellStr(rowStart, columnStart), CellStr(rowEnd, columnEnd));

_range.Value2 = values;

}

   还有我们注意到“m_objRange = m_objSheet.get_Range("A1", "C1");”返回Range的方法里的参数可以看出来，表示范围的方法是用一个“列行”组成的字符串。由于我们习惯用index来定位数组况且Array也只能用数字来定位，所以还必须有一个方法，来完成数字到字母的转化。Excel的首行是第一行，为了使用的习惯，改为由0行作为首行。
         private string CellStr(int rowIndex, int columnIndex)

{

return (char)(65 + columnIndex) + (++rowIndex).ToString();

}

还有一些关于sheet的打开关闭保存等问题就不一一解释了，朋友们一看代码就可以明白了。写完了ExcelHelper我们已经可以用程序来快速的访问excel了。如果用ADO.net的方法，只需要操作返回的DataTable，而如果用Com的方法，也只需要访问返回的Array就可以了。但是现在又有另一个问题产生了。

从DataTable可以获得列信息，那Array呢，怎么样获得呢，Array又是如何的一个结构，如何来操作呢。

这里我们又写了一个辅助的类ArrayCollection。构造函数直接传入两个Array，一个是列信息，一个是行信息，通过ArrayCollection，来将Array转化为List，方便访问，在形式上努力做到和DataTable统一。

这样子以后对于Excel的两种访问方式都写完了，以后还要做一个性能方面的比较，来证实我的想法的正确性，剩下的就是要对返回的数据进行处理了，这个下次在分析了。因为我读取和写入文件都只访问一次文件，而最花时间的地方就在这，所以，如果要加做进度条的话可能就无法实现了，这个问题以后在做考虑吧。

转载于:https://www.cnblogs.com/freewiller/archive/2006/09/10/500326.html