匯入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

規則運算式

用法         含義
\d             數字(0~9)      等同[0-9]
\D            除了數字(0~9)      等同[^0-9]
\w            字母、數字、底線      等同[A-Za-z0-9_]
\W           除了字母、數字、底線      等同[^A-Za-z0-9_]
{n}           限n位
{n,m}      限n至m位
+              重覆一次以上
*               重覆零次以上
\d{5}      表示限五位數數字
\d{1,4}   表示限一位數至四位數數字
\d+         表示限數字
\d*          表示限數字或null

sql語法

一般新增、修改、刪除
insert (into) TableName
(column1, column2, …, columnN )
Values(v1, v2, …, vN)

update TableName
set column1=v1, column2=v2, …, colunmN=vN
from TableName1 a , TableName2 b,…
where condition

delete *
from TableName
where condition

//增加欄位及資料型態
alter table CarCodeInfo
add test1 int
go

//修改欄位資料型態
alter table CarCodeInfo
alter column test1 char(4)
go


//跨server 新增
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=xxx;Password=xxx'
).cartest.dbo.QCItem(QCItemID,CarCategory,TestMethod,Contents,AdopStandard,ToSort,IsShow)
select newid(),CarCategory,TestMethod,Contents,AdopStandard,ToSort,IsShow
from dbo.QCItem
where CarCategory='2' and (TestMethod='9' or TestMethod='10')
//跨server update
update OPENDATASOURCE('SQLOLEDB','Data Source=17-0526277-01;User ID=sa;Password=sa').CarCert0109_online.dbo.carsaledetail
set csd_carmodelname = (select...)
where csd_splyno = '093'

ps：SQL Server2005需設定SQL Server Surface Area Configuration，點選"功能的介面區組態"，勾選"啟用OPENROWSET和OPENDATASOURCE支援"


//查詢完整的table資訊
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='xxxx'

//新增流水號
select Num = identity(int,1,1), * into #temp from TableName
drop table #temp

ps：int為小寫 1,1同識別序號用法

//sql中使用indexof的寫法
charindex(exp1, exp2 [, start location])
exp1 => 目標字
exp2 => 搜尋字串
start location => exp2開始的位置
ex：
select charindex('2','3521468524') ==> 3
select charindex('2','3521468524',4) ==> 9 (因為從第4個位置開始找，所以第一個找到的2在位置9)

自動執行

如果需要系統每天更新資料
可以在 global.cs 下處理
/*
public class Global : System.Web.HttpApplication
{
    //宣告timer
    private System.Timers.Timer ConveyAuto;
    private DateTime lastRemiderCheckTime;

    #region 啟動timer
    ConveyAuto = new System.Timers.Timer();

    //要執行的程式
    ConveyAuto.Elapsed+=new ElapsedEventHandler(AutoHandler);

     //設定時間(dd,hh,mm,ss,ff)
    TimeSpan tsReminder = new TimeSpan(0, 0, 0, 0, 1);

    //引發 Elapsed 事件的時間間隔 (單位為毫秒)。預設值是 100 毫秒。
    ConveyAuto.Interval= tsReminder.Milliseconds;
    ConveyAuto.Enabled=true;
    #endregion


    #region 自動轉檔
    private void AutoHandler(object source, ElapsedEventArgs e)
    {
        DateTime dt = DateTime.Now;
        //從 web.config中設定的時間 取得數值
        string[] ConveyTime = System.Configuration.ConfigurationSettings.AppSettings["ConveyTime"].ToString().Split(',');
        int h = int.Parse(ConveyTime[0].ToString());
        int m = int.Parse(ConveyTime[1].ToString());
        int s = int.Parse(ConveyTime[2].ToString());
        TimeSpan tmUpd = new TimeSpan(h,m,s);
        // 每天 hh:mm:ss 執行
        if( (dt.TimeOfDay == tmUpd) && (lastRemiderCheckTime.Date < dt.Date) )
        {
            ((System.Timers.Timer)source).Enabled=false;
            lastRemiderCheckTime = dt;
            //合格證轉檔
            ConveyFunction CF = new ConveyFunction();
            CF.Convey(System.DateTime.Now.ToString("yyyyMMdd"), "A");

            ((System.Timers.Timer)source).Enabled=true;
        }
    }
    #endregion
}
*/

新建專案

1.在server上建立專案名稱的空資料夾名稱
2.打開vss admin
Tools\Create Database 目的選server上建立的專案名稱
Users\Open SourceSafe Database 建立使用者
3.新增空白專案 逐一加入現有專案：
ex：專案、ASPnetMenu、ProjectDocument、SqlCommandGallery、UIComponent
全部加入後先中斷原繫結路徑，再逐一點選專案 檔案->原始檔控制->將專案加入至原始檔控制
4.進入iis 設定虛擬路徑 選擇目的資料夾即可