讀取excel的欄位數目(不使用oledb)

讀取excel的欄位數目(不使用oledb)




這次遇到的問題是當excel中欄位數超過255欄時，以前用oledb讀入的方式會產生錯誤，執行到fill(ds)時就會掛掉了，連用try-catch都抓不出錯誤，就直接給我罷工(氣死)!!


經過google後援會一番商討後，終於看到了點希望，就在差點搞掛電腦ㄉ危險下終於ok!! 呼~~~


讓我寫下這可歌可泣ㄉ一面

~step1~

加入參考

1 .NET System.Data.OracleClient.dll

2. COM Microsoft Excel 11.0 Object Library

~step2~


加入namespace


using Excel;


using System.Configuration;


using System.Reflection;


using System.Diagnostics;

~step3~


object missing = Missing.Value;

Excel.ApplicationClass app;

Excel.Workbook book;

Excel.Worksheet sheet;

int excelcount;



app = new ApplicationClass();
try
{
book = app.Workbooks.Open(Files[j],missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing,missing,missing);
}
catch (Exception e)
{
throw e;
}
//得到WorkSheet
sheet = (Excel.Worksheet)book.Sheets.get_Item(1);

//得到使用過的欄位數目
excelcount = sheet.UsedRange.Columns.Count;
book.Close(false,false,false);
app.Workbooks.Close();
app.Quit();

ps：記得application一定要關!! 不然會一直執行!! 無法測試，必須重開機，以上!!

匯入Excel

說明：
利用HTTP上傳檔案物件來匯入EXCEL
直接讀取資料使用
不過只限標準格式的XLS

注意：
web.config 中如果有權限設定存在時 需設為false，如下
identity impersonate="false"
---------------------------------------------------------------------------------

範例：

//從上傳路徑中捉取檔案名稱
string FileName = FileSource.PostedFile.FileName.Substring(FileSource.PostedFile.FileName.LastIndexOf("\\")+1, FileSource.PostedFile.FileName.Length-FileSource.PostedFile.FileName.LastIndexOf("\\")-1);
//若資料夾不存在 新增資料夾
if (!Directory.Exists(Global.strFileSavePath))
{ Directory.CreateDirectory(Global.strFileSavePath);}

string tmpFile = string.Format(@"{0}{1}", Global.strFileSavePath, FileName);
//另存檔案至資料夾 才可進行匯入dbㄉ動作
FileSource.PostedFile.SaveAs(tmpFile);
string xlsFullName = tmpFile;

DataSet ds = new DataSet();

string OleConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+xlsFullName+";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";

OleDbConnection OleCn = new OleDbConnection(OleConStr);
OleDbCommand OleCmd = new OleDbCommand();
OleCmd.Connection = OleCn;
string strSQL = @"SELECT * FROM [Sheet1$]"; //依工作表名稱決定
OleCmd.CommandText = strSQL;

try
{
OleCn.Open();
OleDbDataAdapter oledbda = new OleDbDataAdapter(strSQL, OleCn);
oledbda.Fill(ds);
}
catch
{
String scriptString = @"

";
this.RegisterStartupScript("s",scriptString);
return;
}

DataImport oD = new DataImport(CarISCDB, CarISCCmd);

for(int i=1;i<….Count;i++) //i設為1 因為title不儲存
{
//照資料庫欄位寫法
oD.CarCategory = ds.Tables[0].Rows[i]["F1"].ToString();//"F1"指excel第一個欄位
oD.Organization = ds.Tables[0].Rows[i]["F2"].ToString();
oD.CertificateNum = ds.Tables[0].Rows[i]["F3"].ToString();
oD.Comp_IDNum = ds.Tables[0].Rows[i]["F4"].ToString();
. oD.HCEVAP_R = ds.Tables[0].Rows[i]["F58"].ToString();
//新增或更新資料到SQL Server
oD.CertiImportC();
}

匯出Excel~~~不使用tool產生html格式的xls

說明：
直接寫成html的格式
然後匯出成excel的格式
------------------------------------------------------------------------------

範例：

using System.IO;

Response.Clear();
Response.Charset = "big5";
Response.AddHeader("Content-Disposition",
"attachment;filename="+Server.UrlEncode(FileNameC)
);
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("big5");
Response.Write("<@html><@body>"); //拿掉@
Response.Write("<@meta http-equiv=Content-Type content=text/html; charset=big5>");//拿掉@
Response.Write("<@style type=text/css>");//拿掉@
Response.Write("td{mso-number-format:\"\\@\";}");
Response.Write(".formCaption1={back-color=#CECFCE;font-size=12px;height=24px;}");
Response.Write("");//拿掉@

System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter tw = new System.Web.UI.HtmlTextWriter(sw);
sw.WriteLine("");

sw.WriteLine("");
sw.WriteLine("

合格證資料轉出(批次-汽車)

列印日期:"+DateTime.Now.ToString("yyyy/MM/dd")+"

");
//title
sw.WriteLine(@"");

//detail
sw.WriteLine(@"");
sw.WriteLine("

...

...

");
Response.Write("

"); Response.Write(sw.ToString()); Response.Write("

");
Response.Write("");//拿掉@
Response.End();

匯出Excel~~~不使用tool產生標準excel格式的xls

前置：
需要準備一個excel的範本，標題同匯出結果的標題(類似ExcelSpeedGen的做法)
需把唯讀勾掉
--------------------------------------------------------------------------------

說明：
在程式中利用範本複製一份到temp資料夾
然後一筆一筆insert data進去複製的excel中
再將temp資料夾的excel匯出 最後刪除
--------------------------------------------------------------------------------

範例：

using System.Data.OleDb

System.Data.OleDb.OleDbCommand cmd;
System.IO.FileInfo fileinfo ; //從程式中抓取樣版檔案
if(sCarCategory == "1")
{
fileinfo = new System.IO.FileInfo(Server.MapPath("ExportCarISC_G.xls"));
}
else
{
fileinfo = new System.IO.FileInfo(Server.MapPath("ExportCarISC_M.xls"));
}
//複製一份到temp資料夾
fileinfo.CopyTo(Global.strFileSavePath + sFileName);
string Conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Global.strFileSavePath + sFileName + ";" + "Extended Properties='Excel 8.0;HDR=Yes;MAXSCANROWS=16;Persist Security Info=False'";
System.Data.OleDb.OleDbConnection ExcelConn = new System.Data.OleDb.OleDbConnection(Conn);
DataExport csDE = new DataExport(CarCertifDB, CarCertifCmd);
DataSet ds = csDE.C_GetData(UserID, IDNo);
string dcN="", dcV="", sql="";
#region Title
dcN += @"[車輛種類],[審驗單位],[合格證明編號],[委託客戶統編],[委託客戶名稱], . . [HC(ppm)],[轉速(rpm)],[蒸發HC(g/test)] ";
#endregion

#region detail
for(int i=0 ;i<….Count;i++)
{
dcV="";
dcV += "'"+ds.Tables[0].Rows[i]["CarCategory"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CertiUnit"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CertificateNo"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["Comp_IDNum"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["CName"].ToString()+"'," ... +"'"+ds.Tables[0].Rows[i]["LazyHC"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["LazySpeed"].ToString()+"'," +"'"+ds.Tables[0].Rows[i]["HCEVAP_R"].ToString()+"'";
sql = "Insert into [Sheet1$] (" + dcN + ") values (" + dcV + ")";
cmd = new System.Data.OleDb.OleDbCommand(sql, ExcelConn);
if(ExcelConn.State == ConnectionState.Closed)
ExcelConn.Open();
cmd.ExecuteNonQuery();
}
#endregion
ExcelConn.Close();
//從temp資料夾抓取檔案
fileinfo = new System.IO.FileInfo(Global.strFileSavePath + sFileName);
if(fileinfo.Exists)
{
Response.ClearHeaders();
Response.Clear();
string attachment = "attachment; filename=" + sFileName;
Response.AddHeader("Accept-Language", "zh-tw");
Response.AddHeader("Content-Disposition", attachment);
Response.AddHeader("Content-Length", fileinfo.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(fileinfo.FullName);
Response.Flush();
//刪除temp中該檔案
fileinfo.Delete();
Response.End();
}

匯出Excel~~~使用tool產生標準excel格式的xls-ExcelSpeedGen

前置：
需要準備一個excel的範本，標題同匯出結果的標題
--------------------------------------------------------------------------------

說明：
將sql讀出的資料塞入excel範本
然後匯出excel

以下為asp寫法(asp.net修改完有點問題)
--------------------------------------------------------------------------------

範例：

DIM UserId,sStartDate,sEndDate,sRepeat,sFileName, IDNo, sCarCategory
UserID = Request("UserID")
sStartDate = Request("StartDate")
sEndDate = Request("EndDate")
sRepeat = Request("IsRepeat")
sFileName = Request("FileName")
IDNo = Request("IDNo")
sCarCategory = Request("CarCategory")
Dim CarCategory, Organization, CertificateNum, Comp_IDNum, CName
Dim FinishDate, ApprovalNum, EmissionStandard, IsImport, ClassCode
Dim Manufacturer, Brand, Made_Area, Import_Area, EngineGroup
Dim CarYear, ApplyType, CCarModel, ECarModel, CarModelType
Dim Exhaust, TransmissionType, TransmissionNum, CarDoorCount, ModelNameOthers
Dim PowerSource, BasicEngine, Catalyst, Circulate, EEC
Dim PCV, HotReactor, AirPump, AirControl, Operception
Dim Decelerate, DownEffect, IOControlOther, IOControlComment, Transmission
Dim MaxPower, CarWeight, TestMethod, CO_R, NMHC_R
Dim NOx_R, HC_R, Modulus_Method, Modulus_Type, Modulus_CO
Dim Modulus_NMHC, Modulus_HC, Modulus_NOx, Modulus_Evap, LazyCO
Dim LazyHC, LazySpeed, HCEVAP_R
Dim dbFile ' Database File
Dim oConn ' ADO Connection object
Dim Rs1 ' Record Source 1
Dim XLS ' Excel SpeedGen Object
Dim SrcBook ' Source Workbook ' Open Microsoft Access Database
'dbFile = Server.MapPath("sample.mdb")
oConn = Server.CreateObject("ADODB.Connection")
oConn.Open (Connection_SQL) ' Open the RecordSets
Dim str_SQL str_SQL = "SELECT * FROM CertifExportCarISC WHERE UserID = '" + UserID + "' AND IDNo = '" + IDNo + "' ORDER BY TempNo"
Rs1 = oConn.Execute(str_SQL) ' SQL Statement (or Table name)
' Create Excel File
XLS = Server.CreateObject("XLSpeedGen.ASP")
XLS.EstimatedSize = 100000 ' Set Estimated Output File Size (Critical for speed)
' Set up Aliases
XLS.AddRS_ADO(Rs1, 20) ' RecordSource 1 (read 20 rows at a time)
' Add RecordSources
if sCarCategory = "1" THEN
SrcBook = Server.MapPath("ExportCert_C.xls") ' Location of Source Workbook
else
SrcBook = Server.MapPath("ExportCert_M.xls")
end if
' Generate SpreadSheet and Stream to Client, Open in Place '(True 以 IE 開啟，False 以 Excel 開啟)
XLS.Generate(SrcBook, sFileName, False)
XLS = Nothing ' Destroy object when done
' Cleanup Code
'oConn.close ' Close Connection and all Recordsets
oConn = Nothing