在ASP.NET中读写Excel文件

 

在ASP.NET应用程序常常会遇到需要从Excel文件中读取数据或将数据写入Excel的需求。一般来讲，在ASP.NET中读写Excel文件有四种解决方案。

1.1.1 使用OLE DB

使用OLE DB可以以查询数据库的方式来读取Excel文件，因为在某种程度上Excel表格可以看成是一张一张的数据表。其二者的主要区别在于所使用的数据引擎不一样。使用OLE DB访问Excel的要点是计算机上必须具有Microsoft Access Data Component 2.6(MADC2.6)以上版本，同时在连接字符串上必须声明“Extended Properties=Excel 8.0”，这里的指定Excel 版本号如果高于8.0可能会出错，所以一般来讲必须使用Excel 8.0。

然后其它的写法就和一般的访问数据库一样了，打开连接，填充数据集，再关闭连接即可。例如下面的实现代码：

//创建一个数据链接

string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c://sample.xls;Extended Properties=Excel 8.0" ;

OleDbConnection myConn = new OleDbConnection ( strCon ) ;

string strCom = " SELECT * FROM [Sheet1$] " ;

myConn.Open ( ) ;

//打开数据链接，得到一个数据集

OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;

//创建一个 DataSet对象

myDataSet = new DataSet ( ) ;

//得到自己的DataSet对象

myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;

//关闭此数据链接

myConn.Close ( ) ;

代码 7-1

 

使用这种解决方案的优点是不需要其他的服务器组件，部署非常方便，但是其缺点也是明显的，用它来读取Excel 2003格式以上的文件会存在数据丢失的情况，而且也无法生成Excel文件。

1.1.2 使用Office主互操作程序集

另外一种解决方案是使用Office主互操作程序集，采用这种方式需要在服务器上安装Office 2003。但是能够比较精细的控制Excel文件的方方面面，包括格式、字体、颜色等等。

下面的代码演示了如何读取Excel文件中某个单元格的值：

string excelFilePath=@"D:/Book1.xls";

   Excel.Application myExcel=new Excel.ApplicationClass( ) ;

   object oMissing = System.Reflection.Missing.Value ;

   myExcel.Application.Workbooks.Open(excelFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,    oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing) ;

   Excel.Workbook myBook = myExcel.Workbooks[1] ;

   Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1] ;

   System.Data.DataTable dt=new System.Data.DataTable("mytable");

   dt.Columns.Add("F1", System.Type.GetType("System.String"));

   dt.Columns.Add("F2", System.Type.GetType("System.String"));

   dt.Columns.Add("F3", System.Type.GetType("System.String"));

   dt.Columns.Add("F4", System.Type.GetType("System.String"));

   dt.Columns.Add("F5", System.Type.GetType("System.String"));

   DataSet myDs = new DataSet();

   myDs.Tables.Add(dt);

   DataRow myRow;

   myDs.Clear();

   for( int i = 2 ; i <= 4 ; i ++ ) //第一行为标题，不读取

   {

    myRow = myDs.Tables["mytable"].NewRow();

    for( int j = 1 ; j <= 5 ; j ++ )

    {

     Excel.Range r=(Excel.Range)mySheet.Cells[i,j];

     string strValue=r.Text.ToString();

     string aa=strValue;

     string columnname="F"+j.ToString();

     myRow[columnname]=strValue;

    }

    myDs.Tables["mytable"].Rows.Add(myRow);

   } 

   DataGrid1.DataSource=myDs.Tables["mytable"].DefaultView;

   DataGrid1.DataBind();

代码 7-2

 

而下面的代码则演示了如何生成Excel文件并向其中写入值：

string filename="";

   Excel.ApplicationClass oExcel;

   oExcel = new Excel.ApplicationClass();

   oExcel.UserControl = false;

   Excel.WorkbookClass wb = (Excel.WorkbookClass) oExcel.Workbooks.Add(System.Reflection.Missing.Value);

   for(int i = 1;i <= 5; i++)

   {

    oExcel.Cells[i,1]=i.ToString();

    oExcel.Cells[i,2]="'第2列";

    oExcel.Cells[i,3]="'第3列";

    oExcel.Cells[i,4]="'第4列";

   }  

   wb.Saved = true;

   filename= Request.PhysicalApplicationPath + "test.xls";

   oExcel.ActiveWorkbook.SaveCopyAs(filename);

   oExcel.Quit();

   System.GC.Collect();

   Response.Redirect( Request.ApplicationPath + "/test.xls");

代码7-3

 

实际上对于ASP.NET来讲，这并不是一个很好的解决方案，其原因是这个解决方案将客户端的组件用到了服务器上，这往往会带来一些一向不到的问题，如果在处理一个Excel文件的时候出错，那么整个线程就会死在那里，服务器上的Excel进程无法重启动，Excel文件无法删除，后面的Excel文件也无法处理，只能重启服务器，所以这是一个很大的问题。

1.1.3 使用ServerDocument

在上一个解决方案中，使用Office主互操作程序集存在很大的问题，究其原因是因为将本应在客户端使用的Office组件用在了服务器上导致的，因为这些Office组件在设计之初就没有考虑在服务中调用的情况。对于在服务器上使用Excel文件，微软推荐的解决方案是使用ServerDocument。

在具有 Microsoft Visual Studio 2005 Tools for the Microsoft Office System 自定义的 Microsoft Office Word 2003 文档或 Microsoft Office Excel 2003 工作簿中，可以将数据存储在嵌入的数据岛中，无需启动 Excel 或 Word 即可访问数据岛。

数据岛是一个 XML 文档，其中包含 Office 文档中嵌入的数据；无需实例化 Office 文档即可访问该 XML 文档。数据实际存在于两个地方，即文档和单独的嵌入数据岛中。在数据岛与文档之间使用了数据绑定以使它们保持同步。如果服务器上运行的代码修改了数据岛，则在文档打开并且文档中的代码运行时，Office 文档会与数据岛进行同步。

此模型具有以下几项优势：

l 可以将验证代码添加到独立于文档的数据中。通过将验证与文档分离，可以将数据验证代码移植到其他文档中。

l 数据岛使用可脱机使用的数据填充。当文档中的缓存数据项包含数据时，该文档将与数据岛进行交互。

l 由于可以从外部访问数据岛，因此无需实例化 Office 就可以修改文档中嵌入的数据，从而支持服务器上文档的快速批处理。但是，只能访问缓存中的数据，而不是文档中的所有数据。

下面的代码分别演示了如何使用ServerDocument访问数据和从数据生成Excel文档：

string expenseDoc = @"C:/ExpenseDocuments/Expenses0105.xls";

ServerDocument sd1 = null;

 

try

{

    sd1 = new ServerDocument(expenseDoc);

    CachedDataHostItem dataHostItem1 =

        sd1.CachedData.HostItems["DataNamespace.DataWorksheet"];

 

    CachedDataItem dataItem1 = dataHostItem1.CachedData["DataCache"];

 

    System.IO.StringReader schemaReader = new System.IO.StringReader(dataItem1.Schema);

    System.IO.StringReader xmlReader = new System.IO.StringReader(dataItem1.Xml);

 

    ExpenseData.ReadXmlSchema(schemaReader);

    ExpenseData.ReadXml(xmlReader);

}

finally

{

    if (sd1 != null)

    {

        sd1.Close();

    }

}

代码 7-4

 

string name = @"C:/Documents/WordApplication3.doc";

System.IO.FileStream fileStream = null;

byte[] bytes = null;

 

try

{

    fileStream = new System.IO.FileStream(

        name, System.IO.FileMode.Open, System.IO.FileAccess.Read);

 

    bytes = new byte[(int)fileStream.Length];

 

    fileStream.Read(bytes, 0, (int)fileStream.Length);

}

finally

{

    if (fileStream != null)

    {

        fileStream.Close();

    }

}

ServerDocument sd1 = null;

try

{

    sd1 = new ServerDocument(bytes, name);

 

    // Your data manipulation code goes here.

 

    sd1.Save();

    bytes = sd1.Document;

    // If you have a Word document, use the MIME string:

    Response.ContentType = "application/msword";

 

    // If you have an Excel workbook, use the MIME string:

    //Response.ContentType = "application/vnd.ms-excel";

 

    Response.AddHeader("Content-disposition", "filename=" + name);

    Response.Write(sd1);

}

finally

{

    if (sd1 != null)

    {

        sd1.Close();

    }

}

代码 7-5

 

相对来说，这是一个比较理想的解决方案，但是使用该方案有如下几个限制：

l 客户端必须安装.NET Framework 2.0、Office Tools for Visual Studio 运行时和Office 2003。

l 开发嵌入于文档中的程序集，用于同步视图和数据岛中的数据，例如用数据岛数据中的某个值更新某单元格中的值，或者用某单元格中的值更新数据岛中的值。

l 使用专用的Excel模板。

 

1.1.4 对于Office 2007的OpenXML格式使用OpenXML SDK

如果决定ASP.NET 应用程序仅处理Office 2007生成的OpenXML格式文档，那么使用OpenXML SDK是一个更好的主意，因为它不需要在服务器上安装Office，对客户端也没有任何要求，只要用的是OpenXML格式的文档就可以了，因为OpenXML格式已提交国际标准化组织，所以，用户可以使用支持OpenXML的任何应用程序读取和编辑该文档。

使用该方案需要下载OpenXML SDK并使用其进行开发，目前该SDK还只是一个CTP版本。下面的代码演示了如何使用OpenXML SDK读写OpenXML格式的Excel文件。

// How to: Get worksheet information.

public List<string> XLGetSheetInfo(string fileName)

{

    // Fill this collection with a list of all the sheets

    List<string> sheets = new List<string>();

 

    using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(fileName, false))

    {

        WorkbookPart workbook = xlPackage.WorkbookPart;

        Stream workbookstr = workbook.GetStream();

        XmlDocument doc = new XmlDocument();

        doc.Load(workbookstr);

 

        XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);

        nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);

        XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);

 

        foreach (XmlNode node in nodelist)

       {

            string sheetName = string.Empty;

            sheetName = node.Attributes["name"].Value;

            sheets.Add(sheetName);

        }

    }

    return sheets;

}

代码 7-6

 

// How to: Create a new package as a Excel document.

public static void CreateNewExcelDocument(string document)

{

   using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(document, preadsheetDocumentType. Workbook))

   {

      // Set the content of the document so that Excel can open it.

      WorkbookPart  mainPart = excelDoc. AddWorkbookPart ();

 

      SetMainDocumentContent(mainPart);

   }

}

 

// Set content of MainDocumentPart.

public static void SetMainDocumentContent(WorkbookPart part,string excelXml)

{

    using (Stream stream = part.GetStream())

    {

       byte[] buf = (new UTF8Encoding()).GetBytes(docXml);

       stream.Write(buf, 0, buf.Length);

    }

}

代码 7-7