class ReadWriteExcel
{
#region 读取Excel
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <param name="filepath">Excel文件路径</param>
/// <param name="sheetindex">需要读取的Sheet 例如要Sheet1$，则此处填写 1</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcel(
string filepath,
int sheetindex)
{
DataTable dt_Result =
new DataTable(); ;
string filetype = filepath.Substring(filepath.Length -
4);
string strconn =
"";
switch (filetype)
{
case ".xls":
{
strconn =
"Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath +
"; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
break;
}
case "xlsx":
{
strconn =
"Provider=Microsoft.Ace.OLEDB.12.0;data source=" + filepath +
";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
}
default:
{
strconn =
"Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath +
"; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
break;
}
}
try
{
using (System.Data.OleDb.OleDbConnection conn =
new System.Data.OleDb.OleDbConnection(strconn))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
null);
conn.Close();
string sql =
"";
if (sheetindex !=
1)
{
sql =
"SELECT * FROM [Sheet'" + sheetindex +
"'$] ";
}
else
{
sql =
"SELECT * FROM [Sheet1$] ";
}
new System.Data.OleDb.OleDbDataAdapter(
new System.Data.OleDb.OleDbCommand(sql, conn)).Fill(dt_Result);
}
return dt_Result;
}
catch (Exception ex)
{
return dt_Result;
throw new Exception(
"读取Excel文件失败\n" +
ex.ToString());
}
}
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <param name="filename">Excel文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcel(
string filename)
{
return ReadExcel(filename,
1);
}
#endregion
#region 写入Excel
public static void WriteExcel(DataTable dt)
{
string saveFileName =
"";
bool fileSaved =
false;
SaveFileDialog saveDialog =
new SaveFileDialog();
saveDialog.DefaultExt =
"xlsx";
saveDialog.Filter =
"Excel文件|*.xlsx|Excel文件|*.xls";
saveDialog.FileName =
"Export" + System.DateTime.Now.ToString(
"yyyyMMddhhmmss");
saveDialog.ShowDialog();
saveFileName =
saveDialog.FileName;
if (saveFileName.IndexOf(
":") <
0)
return;
//被点了取消
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
if (xlApp ==
null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks =
xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[
1];
//取得sheet1
//写入字段
for (
int i =
0; i < dt.Columns.Count; i++
)
{
worksheet.Cells[1, i +
1] =
dt.Columns[i].ColumnName;
}
//写入数值
for (
int r =
0; r < dt.Rows.Count; r++
)
{
for (
int i =
0; i < dt.Columns.Count; i++
)
{
worksheet.Cells[r +
2, i +
1] =
dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if (saveFileName !=
"")
{
try
{
workbook.Saved =
true;
workbook.SaveCopyAs(saveFileName);
fileSaved =
true;
}
catch (Exception ex)
{
fileSaved =
false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" +
ex.Message);
}
}
else
{
fileSaved =
false;
}
xlApp.Quit();
GC.Collect();//强行销毁
}
#endregion
}
winform基本上够用了吧
转载于:https://www.cnblogs.com/ShuiMu/articles/3316305.html
相关资源：c#Excel导入导出
