关于ASP.Net的导入导出的[转]
?首先要說一下自己寫的一個幫助類ExcelHelper。可以用兩種方式訪問Excel文件，一種是以Excel的Com組件的形式，一種是最簡單的把Excel看作是數據庫。其實很多的內容網上都已經有了，但都比較分散，都只提到了一種或兩種，沒有統一到一起。代碼如下：
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
????????#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
????????#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/lxinxuan/archive/2006/11/02/547452.html
總結
以上是生活随笔為你收集整理的关于ASP.Net的导入导出的[转]的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Node Buffer 利用 sl
- 下一篇： fs.mkdir