Excel匯入匯出整理
參考資料很多=.=，感謝各位大大無私的分享（洪爺早餐店…）
整理一下結果
取得所有worksheet名稱
/// Gets the name of all sheet.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <returns></returns>
public static List<string> GetAllSheetName(string FName, bool HasFieldName)
{
string strConn;
List<string> sTBList = new List<string>();
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
OleDbConnection odc = new OleDbConnection(strConn);
odc.Open();
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$",string.Empty));
}
}
return sTBList;
}取得第一個worksheet
/// Gets the first name of the sheet.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <returns></returns>
public static string GetFirstSheetName(string FName, bool HasFieldName)
{
string strConn;
List<string> sTBList = new List<string>();
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
OleDbConnection odc = new OleDbConnection(strConn);
odc.Open();
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
sTBList.Add(dr["TABLE_NAME"].ToString().Replace("$", string.Empty));
}
}
return sTBList[0];
}匯入excel資料到DataTable
/// Imports the excel.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <returns></returns>
public static System.Data.DataTable ImportExcel(string FName, bool HasFieldName)
{
try
{
List<string> sTBList = new List<string>();
string strConn;
/* strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + FName +
";Extended Properties=Excel 8.0;";*/
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
OleDbConnection odc = new OleDbConnection(strConn);
odc.Open();
DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
sTBList.Add(dr["TABLE_NAME"].ToString());
}
}
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sTBList[0] + "]", strConn);
//OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
System.Data.DataTable myDataSet = new System.Data.DataTable();
myCommand.Fill(myDataSet);
odc.Close();
return myDataSet;
}
catch (Exception e)
{
return null;
}
}匯入excel資料到List<string>
/// Imports the excel to list.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="TableName">Name of the table.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <param name="delimiter">The delimiter.</param>
/// <returns></returns>
public static List<string> ImportExcelToList(string FName, string TableName, bool HasFieldName, string delimiter)
{
List<string> result = new List<string>();
string strConn;
/* strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + FName +
";Extended Properties=Excel 8.0;";*/
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
using (OleDbConnection cn = new OleDbConnection(strConn))
{
cn.Open();
List<string> sTBList = new List<string>();
DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
sTBList.Add(dr["TABLE_NAME"].ToString());
}
}
//工作表名稱需要加$字串
//string qs = "select * from[" + SheetName + "$]";
string qs = "select * from[" + sTBList[0] + "]";
try
{
using (OleDbCommand cmd = new OleDbCommand(qs, cn))
{
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
string TempString = "";
//int Col = dr.FieldCount;
for (int i = 0; i < dr.FieldCount; i++)
{
TempString += dr[i].ToString() + delimiter;
}
result.Add(TempString);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return result;
}建立worksheet
/// Creates the excel sheet.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="TableName">Name of the table.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
public static void CreateExcelSheet(string FName, string TableName, string SheetName, bool HasFieldName)
{
string strConn;
/* strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + FName +
";Extended Properties=Excel 8.0;";*/
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
string ColumnName = NewMediaTest1.Model.DbOperation.GetColumnName(TableName);
string[] ColTemp = ColumnName.Split(',');
// 用OldDb方式去建table
string ExcelColumnName = string.Join(" text , ", ColTemp);
ExcelColumnName += " text ";
using (OleDbConnection cn = new OleDbConnection(strConn))
{
cn.Open();
//工作表名稱需要加$字串
//建立工作表
string qs = " CREATE TABLE " + SheetName + " (" + ExcelColumnName + " ) ";
try
{
using (OleDbCommand cmd = new OleDbCommand(qs, cn))
{
//新增Excel工作表
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}在worksheet中新增一行
/// Inserts the single line excel sheet.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <param name="InsertData">The insert data.</param>
public static void InsertSingleLineExcelSheet(string FName, string SheetName, bool HasFieldName, params string[] InsertData)
{
string strConn;
/* strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + FName +
";Extended Properties=Excel 8.0;";*/
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
string InsertString = NewMediaTest1.Model.DbOperation.InsertDataString(InsertData);
// 用OldDb方式去建table
using (OleDbConnection cn = new OleDbConnection(strConn))
{
cn.Open();
//工作表名稱需要加$字串
//建立工作表
string qs = "INSERT INTO [" + SheetName + "$] VALUES( " + InsertString + " )";
try
{
using (OleDbCommand cmd = new OleDbCommand(qs, cn))
{
//增加資料
// cmd.CommandText = "INSERT INTO [" + SheetName + "$] VALUES( " +InsertString+" )";
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}更新worksheet一行
/// Updates the sheet single line.
/// </summary>
/// <param name="FName">Name of the F.</param>
/// <param name="TableName">Name of the table.</param>
/// <param name="SheetName">Name of the sheet.</param>
/// <param name="HasFieldName">if set to <c>true</c> [has field name].</param>
/// <param name="Condition">The condition.</param>
/// <param name="UpdateData">The update data.</param>
public static void UpdateSheetSingleLine(string FName,string TableName, string SheetName,bool HasFieldName,string Condition, params string[] UpdateData)
{
string strConn;
if (HasFieldName)
/*如果Excel中的第一列為欄名,則寫成*/
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
string UpdateDataString = NewMediaTest1.Model.DbOperation.UpdateDataString(UpdateData, TableName);
string WhereCondition = "";
if(Condition != "")
WhereCondition = " where " + Condition ;
string qs1 = "Update [" + SheetName + "$] set " + UpdateDataString + WhereCondition;
//1.建立連線
using (OleDbConnection cn = new OleDbConnection(strConn))
{
cn.Open();
//2.建立OleDbCommand物件
using (OleDbCommand cm = new OleDbCommand(qs1, cn))
{
cm.ExecuteNonQuery();
}
}
}匯出excel for windowfrom
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int FindWindow(string strclassName, string strWindowName);
/// <summary>
/// Exports the excel.
/// </summary>
/// <param name="ds">The ds.</param>
/// <param name="ListName">Name of the list.</param>
/// <param name="AddTitle">if set to <c>true</c> [add title].</param>
public static void ExportExcel(System.Data.DataTable ds, string[] ListName, bool AddTitle)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Caption = "ExportExcel";
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet ExcelSheets;
ExcelSheets = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);
if (AddTitle == true)
{
for (int q = 0; q < ListName.Length; q++)
ExcelSheets.Cells[1, q + 1] = ListName[q].ToString();
}
object missing = Missing.Value;
excel.DisplayAlerts = false;
excel.Visible = false;
int RoLength = ds.Rows.Count;
int i, j;
for (i = 0; i < RoLength; i++)
{
for (j = 0; j < ListName.Length; j++)
{
string value = ds.Rows[i][j].ToString();
if (AddTitle == true)
ExcelSheets.Cells[i + 2, j + 1] = value;
else
ExcelSheets.Cells[i + 1, j + 1] = value;
}
}//for
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel files(*.xls)|*.xls|All files(*.*)|*.*";
saveFileDialog.Title = "test";
saveFileDialog.FilterIndex = 1;
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
ExcelSheets.SaveAs(saveFileDialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, false, false, false, Type.Missing, Type.Missing, true);
}//if()
//針對標題去找EXCEL程式的PID(唯一值),最後會依靠PID去關閉EXCEL程式
IntPtr t = new IntPtr(FindWindow("XLMAIN", excel.Caption));
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p_excel = System.Diagnostics.Process.GetProcessById(k);
excel.Workbooks.Close();
excel.Quit();
p_excel.Kill();
excel = null;
p_excel = null;
}//try
catch (System.Exception e)
{
return null;
}
}合併worksheet
/// Merges the sheet from file.
/// </summary>
/// <param name="SourceFile1">The source file1.</param>
/// <param name="SourceFile2">The source file2.</param>
/// <param name="Destiation">The destiation.</param>
/// <param name="AppendInFirst">if set to <c>true</c> [append in first].</param>
/// <param name="Source2SheetName">Name of the source2 sheet.</param>
public static void MergeSheetFromFile(string SourceFile1, string SourceFile2, string Destiation,bool AppendInFirst,string Source2SheetName)
{
object missing = Missing.Value;
string oFirstXls = SourceFile1;//excel檔1
string oSecondXls = SourceFile2;//excel檔2
string oOutputXls = Destiation;//合併檔,excel檔3
string SheetName = "";
if (Source2SheetName == "")
SheetName = "Sheet1";
else
SheetName = string.Copy(Source2SheetName);
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook wbook1 = excelApp.Workbooks.Open(oFirstXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//載入excel檔1
Excel.Workbook wbook2 = excelApp.Workbooks.Open(oSecondXls, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//載入excel檔2
if(!AppendInFirst)
wbook1.Worksheets.Copy(missing, wbook2.Sheets[SheetName]);//在excel檔2的Sheet1之後插入所有excel檔1的Sheet
else
wbook1.Worksheets.Copy(wbook2.Sheets[SheetName], missing);//在excel檔2的Sheet1之前插入所有excel檔1的Sheet
wbook2.SaveAs(oOutputXls, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);//另存excel檔3
wbook1.Close(missing, missing, missing);
wbook2.Close(missing, missing, missing);
excelApp.Quit();
}