參考資料很多=.=,感謝各位大大無私的分享(洪爺早餐店…)
整理一下結果
取得所有worksheet名稱
02
/// Gets the name of all sheet.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06
/// <returns></returns>
07
public
static
List<
string
> GetAllSheetName(
string
FName,
bool
HasFieldName)
10
List<
string
> sTBList =
new
List<
string
>();
13
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
15
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
16
OleDbConnection odc =
new
OleDbConnection(strConn);
18
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new
object
[] {
null
,
null
,
null
,
"TABLE"
});
19
if
(dt.Rows.Count > 0)
21
foreach
(DataRow dr
in
dt.Rows)
23
sTBList.Add(dr[
"TABLE_NAME"
].ToString().Replace(
"$"
,
string
.Empty));
取得第一個worksheet
02
/// Gets the first name of the sheet.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06
/// <returns></returns>
07
public
static
string
GetFirstSheetName(
string
FName,
bool
HasFieldName)
10
List<
string
> sTBList =
new
List<
string
>();
13
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
15
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
16
OleDbConnection odc =
new
OleDbConnection(strConn);
18
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new
object
[] {
null
,
null
,
null
,
"TABLE"
});
19
if
(dt.Rows.Count > 0)
21
foreach
(DataRow dr
in
dt.Rows)
23
sTBList.Add(dr[
"TABLE_NAME"
].ToString().Replace(
"$"
,
string
.Empty));
匯入excel資料到DataTable
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
06
/// <returns></returns>
07
public
static
System.Data.DataTable ImportExcel(
string
FName,
bool
HasFieldName)
11
List<
string
> sTBList =
new
List<
string
>();
18
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
20
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
21
OleDbConnection odc =
new
OleDbConnection(strConn);
23
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new
object
[] {
null
,
null
,
null
,
"TABLE"
});
24
if
(dt.Rows.Count > 0)
26
foreach
(DataRow dr
in
dt.Rows)
28
sTBList.Add(dr[
"TABLE_NAME"
].ToString());
31
OleDbDataAdapter myCommand =
new
OleDbDataAdapter(
"SELECT * FROM ["
+ sTBList[0] +
"]"
, strConn);
33
System.Data.DataTable myDataSet =
new
System.Data.DataTable();
34
myCommand.Fill(myDataSet);
匯入excel資料到List<string>
02
/// Imports the excel to list.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="TableName">Name of the table.</param>
06
/// <param name="SheetName">Name of the sheet.</param>
07
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08
/// <param name="delimiter">The delimiter.</param>
09
/// <returns></returns>
10
public
static
List<
string
> ImportExcelToList(
string
FName,
string
TableName,
bool
HasFieldName,
string
delimiter)
12
List<
string
> result =
new
List<
string
>();
19
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
21
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
22
using
(OleDbConnection cn =
new
OleDbConnection(strConn))
25
List<
string
> sTBList =
new
List<
string
>();
26
DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new
object
[] {
null
,
null
,
null
,
"TABLE"
});
27
if
(dt.Rows.Count > 0)
29
foreach
(DataRow dr
in
dt.Rows)
31
sTBList.Add(dr[
"TABLE_NAME"
].ToString());
36
string
qs =
"select * from["
+ sTBList[0] +
"]"
;
39
using
(OleDbCommand cmd =
new
OleDbCommand(qs, cn))
41
using
(OleDbDataReader dr = cmd.ExecuteReader())
45
string
TempString =
""
;
47
for
(
int
i = 0; i < dr.FieldCount; i++)
49
TempString += dr[i].ToString() + delimiter;
51
result.Add(TempString);
58
MessageBox.Show(ex.Message);
建立worksheet
02
/// Creates the excel sheet.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="TableName">Name of the table.</param>
06
/// <param name="SheetName">Name of the sheet.</param>
07
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08
public
static
void
CreateExcelSheet(
string
FName,
string
TableName,
string
SheetName,
bool
HasFieldName)
16
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
18
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
20
string
ColumnName = NewMediaTest1.Model.DbOperation.GetColumnName(TableName);
21
string
[] ColTemp = ColumnName.Split(
','
);
23
string
ExcelColumnName =
string
.Join(
" text , "
, ColTemp);
24
ExcelColumnName +=
" text "
;
25
using
(OleDbConnection cn =
new
OleDbConnection(strConn))
30
string
qs =
" CREATE TABLE "
+ SheetName +
" ("
+ ExcelColumnName +
" ) "
;
33
using
(OleDbCommand cmd =
new
OleDbCommand(qs, cn))
36
cmd.ExecuteNonQuery();
41
MessageBox.Show(ex.Message);
在worksheet中新增一行
02
/// Inserts the single line excel sheet.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="SheetName">Name of the sheet.</param>
06
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
07
/// <param name="InsertData">The insert data.</param>
08
public
static
void
InsertSingleLineExcelSheet(
string
FName,
string
SheetName,
bool
HasFieldName,
params
string
[] InsertData)
16
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
18
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
19
string
InsertString = NewMediaTest1.Model.DbOperation.InsertDataString(InsertData);
21
using
(OleDbConnection cn =
new
OleDbConnection(strConn))
26
string
qs =
"INSERT INTO ["
+ SheetName +
"$] VALUES( "
+ InsertString +
" )"
;
29
using
(OleDbCommand cmd =
new
OleDbCommand(qs, cn))
36
cmd.ExecuteNonQuery();
45
MessageBox.Show(ex.Message);
更新worksheet一行
02
/// Updates the sheet single line.
04
/// <param name="FName">Name of the F.</param>
05
/// <param name="TableName">Name of the table.</param>
06
/// <param name="SheetName">Name of the sheet.</param>
07
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
08
/// <param name="Condition">The condition.</param>
09
/// <param name="UpdateData">The update data.</param>
10
public
static
void
UpdateSheetSingleLine(
string
FName,
string
TableName,
string
SheetName,
bool
HasFieldName,
string
Condition,
params
string
[] UpdateData)
15
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""
;
17
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ FName +
";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1;/""
;
18
string
UpdateDataString = NewMediaTest1.Model.DbOperation.UpdateDataString(UpdateData, TableName);
19
string
WhereCondition =
""
;
21
WhereCondition =
" where "
+ Condition ;
22
string
qs1 =
"Update ["
+ SheetName +
"$] set "
+ UpdateDataString + WhereCondition;
24
using
(OleDbConnection cn =
new
OleDbConnection(strConn))
28
using
(OleDbCommand cm =
new
OleDbCommand(qs1, cn))
匯出excel for windowfrom
01
[DllImport(
"User32.dll"
, CharSet = CharSet.Auto)]
02
public
static
extern
int
GetWindowThreadProcessId(IntPtr hwnd,
out
int
ID);
03
[DllImport(
"User32.dll"
, CharSet = CharSet.Auto)]
04
public
static
extern
int
FindWindow(
string
strclassName,
string
strWindowName);
08
/// <param name="ds">The ds.</param>
09
/// <param name="ListName">Name of the list.</param>
10
/// <param name="AddTitle">if set to <c>true</c> [add title].</param>
11
public
static
void
ExportExcel(System.Data.DataTable ds,
string
[] ListName,
bool
AddTitle)
15
Microsoft.Office.Interop.Excel.Application excel =
new
Microsoft.Office.Interop.Excel.Application();
16
excel.Caption =
"ExportExcel"
;
17
excel.Application.Workbooks.Add(
true
);
18
Microsoft.Office.Interop.Excel.Worksheet ExcelSheets;
19
ExcelSheets = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);
22
for
(
int
q = 0; q < ListName.Length; q++)
23
ExcelSheets.Cells[1, q + 1] = ListName[q].ToString();
25
object
missing = Missing.Value;
26
excel.DisplayAlerts =
false
;
27
excel.Visible =
false
;
28
int
RoLength = ds.Rows.Count;
30
for
(i = 0; i < RoLength; i++)
32
for
(j = 0; j < ListName.Length; j++)
34
string
value = ds.Rows[i][j].ToString();
36
ExcelSheets.Cells[i + 2, j + 1] = value;
38
ExcelSheets.Cells[i + 1, j + 1] = value;
41
SaveFileDialog saveFileDialog =
new
SaveFileDialog();
42
saveFileDialog.Filter =
"Excel files(*.xls)|*.xls|All files(*.*)|*.*"
;
43
saveFileDialog.Title =
"test"
;
44
saveFileDialog.FilterIndex = 1;
45
saveFileDialog.RestoreDirectory =
true
;
46
if
(saveFileDialog.ShowDialog() == DialogResult.OK)
48
ExcelSheets.SaveAs(saveFileDialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, Type.Missing, Type.Missing,
false
,
false
,
false
, Type.Missing, Type.Missing,
true
);
51
IntPtr t =
new
IntPtr(FindWindow(
"XLMAIN"
, excel.Caption));
53
GetWindowThreadProcessId(t,
out
k);
54
System.Diagnostics.Process p_excel = System.Diagnostics.Process.GetProcessById(k);
55
excel.Workbooks.Close();
61
catch
(System.Exception e)
合併worksheet
02
/// Merges the sheet from file.
04
/// <param name="SourceFile1">The source file1.</param>
05
/// <param name="SourceFile2">The source file2.</param>
06
/// <param name="Destiation">The destiation.</param>
07
/// <param name="AppendInFirst">if set to <c>true</c> [append in first].</param>
08
/// <param name="Source2SheetName">Name of the source2 sheet.</param>
09
public
static
void
MergeSheetFromFile(
string
SourceFile1,
string
SourceFile2,
string
Destiation,
bool
AppendInFirst,
string
Source2SheetName)
11
object
missing = Missing.Value;
12
string
oFirstXls = SourceFile1;
13
string
oSecondXls = SourceFile2;
14
string
oOutputXls = Destiation;
15
string
SheetName =
""
;
16
if
(Source2SheetName ==
""
)
19
SheetName =
string
.Copy(Source2SheetName);
21
Excel.Application excelApp =
new
Excel.ApplicationClass();
22
Excel.Workbook wbook1 = excelApp.Workbooks.Open(oFirstXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
23
Excel.Workbook wbook2 = excelApp.Workbooks.Open(oSecondXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
25
wbook1.Worksheets.Copy(missing, wbook2.Sheets[SheetName]);
27
wbook1.Worksheets.Copy(wbook2.Sheets[SheetName], missing);
28
wbook2.SaveAs(oOutputXls, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
29
wbook1.Close(missing, missing, missing);
30
wbook2.Close(missing, missing, missing);