在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主要 Interop 組件

另外一種解決方案是使用Office主要 Interop 組件，採用這種方式需要在伺服器上安裝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主要 Interop 組件存在很大的問題，究其原因是因為將本應在
用戶端使用的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);

    }

}