/**////?<summary>
///?導出到?Excel?文件
///?</summary>
///?<param?name="fileName">含完整路徑</param>
///?<param?name="dataTable">含字段標題名</param>
public?void?ExpExcel(string?fileName?,DataTable?dataTable)
{
????Excel.ApplicationClass?apc?=new?Excel.ApplicationClass();

????apc.Visible?=?false?;
????Excel.Workbook?wkbook?=?apc.Workbooks.Add(?true?)?;
????Excel.Worksheet?wksheet?=?(Excel.Worksheet)wkbook.ActiveSheet;

????int?rowIndex?=?2;
????int?colIndex?=?1;

????wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat?=?"@";

????//取得列標題
????foreach?(DataColumn?dc?in?dataTable.Columns)
????{
????????colIndex?++;
????????wksheet.Cells[1,colIndex]?=?dc.ColumnName;
????}

????//取得表格中數據
????foreach?(DataRow?dr?in?dataTable.Rows)
????{
????????colIndex?=?1;
????????foreach?(DataColumn?dc?in?dataTable.Columns)
????????{
????????????if(dc.DataType?==?System.Type.GetType("System.DateTime"))
????????????{
????????????????apc.Cells[rowIndex,colIndex]?=?"'"+(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
????????????}
????????????else
????????????????if(dc.DataType?==?System.Type.GetType("System.String"))
????????????{
????????????????apc.Cells[rowIndex,colIndex]?=?"'"+dr[dc.ColumnName].ToString();
????????????}
????????????else
????????????{
????????????????apc.Cells[rowIndex,colIndex]?=?"'"+dr[dc.ColumnName].ToString();
????????????}

????????????wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignLeft;

????????????colIndex++;
????????}
????????rowIndex++;
????}
????
????//設置表格樣式
????wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex?=?20;?
????wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex?=?3;
????wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight?=?Excel.XlBorderWeight.xlThin;
????wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).Columns.AutoFit();

????if(File.Exists(fileName))
????{
????????File.Delete(fileName);
????}

????wkbook.SaveAs(?fileName?,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,?Excel.XlSaveAsAccessMode.xlNoChange?,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
???
????wkbook.Close(Type.Missing,Type.Missing,Type.Missing);
????apc.Quit();
????wkbook?=?null;
????apc?=?null;
????GC.Collect();
}
/**////?<summary>
///?從Excel導入帳戶(逐單元格讀取)
///?</summary>
///?<param?name="fileName">完整路徑名</param>
public?IList?ImpExcel(string?fileName)
{
????IList?alExcel?=?new?ArrayList();
????UserInfo?userInfo?=?new?UserInfo();

????Excel.Application?app;
????Excel.Workbooks?wbs;
????Excel.Worksheet?ws;

????app?=?new?Excel.Application();
????wbs?=?app.Workbooks;
????wbs.Add(fileName);
????ws=?(Excel.Worksheet)app.Worksheets.get_Item(1);
????int?a?=?ws.Rows.Count;
????int?b?=?ws.Columns.Count;
????
????for?(?int?i?=?2;?i?<?4;?i++)
????{
????????for?(?int?j?=?1;?j?<?21;?j++)
????????{
????????????Excel.Range?range?=?ws.get_Range(app.Cells[i,j],app.Cells[i,j]);
????????????range.Select();
????????????alExcel.Add(?app.ActiveCell.Text.ToString()?);
????????}
????}

????return?alExcel;
}


/**////?<summary>
///?從Excel導入帳戶(新建oleDb連接,Excel整表讀取,適于無合并單元格時)
///?</summary>
///?<param?name="fileName">完整路徑名</param>
///?<returns></returns>
public?DataTable?ImpExcelDt?(string?fileName)
{
????string?strCon?=?"?Provider?=?Microsoft.Jet.OLEDB.4.0?;?Data?Source?=?"?+?fileName?+?";Extended?Properties=Excel?8.0"?;
????OleDbConnection?myConn?=?new?OleDbConnection?(?strCon?)?;
????string?strCom?=?"?SELECT?*?FROM?[Sheet1$]?"?;
????myConn.Open?(?)?;
????OleDbDataAdapter?myCommand?=?new?OleDbDataAdapter?(?strCom?,?myConn?)?;
????DataSet?myDataSet?=?new?DataSet?(?)?;
????myCommand.Fill?(?myDataSet?,?"[Sheet1$]"?)?;
????myConn.Close?(?)?;

????DataTable?dtUsers?=?myDataSet.Tables[0];

????return?dtUsers;
}


dataGrid中顯示：
DataGrid1.DataMember=?"[Sheet1$]"?;
DataGrid1.DataSource?=?myDataSet?;

轉載于:https://www.cnblogs.com/liuzhixian/articles/851983.html

總結

以上是生活随笔為你收集整理的c#中Excel数据的导入、导出的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。