1.添加office的引用
Microsoft.Office.Interop.Excel
2.读excel文件
//加载excel,读取excel中的数据
/// <summary>
///
/// </summary>
/// <param name="path">文件路径</param>
/// <param name="leixing">文件所选择的类型</param>
/// <returns></returns>
public DataTable ReadExcel(string path, string leixing)
{
DataTable dt = new DataTable();
try
{
string strconn;
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strconn);
string sql = "";
if (leixing == "降水成果表")
{
sql = "select * from [Sheet1$A2:J24]";//读取excel操作
}
if (leixing == "降水成果_szy")
{
sql = "select * from [Sheet1$]";//读取excel操作
}
if (leixing == "降水成果_xz")
{
sql = "select * from [Sheet1$]";//读取excel操作
}
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet1");
OleConn.Close();
dt = OleDsExcle.Tables[0];
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
return dt;
}
3.写excel文件
/// <summary>
/// 将数据写入excel中的特定单元格中
/// </summary>
/// <param name="path"></param>
/// <param name="leixing"></param>
/// <returns></returns>
public void WriteExcel(string path, string leixing, DataTable tt)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets[1];
if (leixing == "降水成果表")
{
FileInfo fi = new FileInfo(path);
if (fi.Exists)
{
//Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range("F4", Missing.Value);
//rng.Value2 = "10";
for (int i = 2; i < tt.Rows.Count; i++)
{
//
for (int j = 5; j < tt.Columns.Count; j++)
{
//Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range(worksheet.Cells[i+1][j], Missing.Value);
//rng.Value2 = tt.Rows[i][j].ToString();
string data = tt.Rows[i][j].ToString();
worksheet.Cells[j+1][i+2] = data;
}
}
worksheet.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.ActiveWorkbook.Close(false, null, null); // 关闭 Excel 文件且不保存
app.Quit();//退出excel
}
//Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range("F4", Missing.Value);
//rng.Value2 = "10";
}
if (leixing == "降水成果_szy")
{
FileInfo fi = new FileInfo(path);
if (fi.Exists)
{
for (int i = 2; i < tt.Rows.Count; i++)
{
//
for (int j = 4; j < tt.Columns.Count; j++)
{
//Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range(worksheet.Cells[i+1][j], Missing.Value);
//rng.Value2 = tt.Rows[i][j].ToString();
string data = tt.Rows[i][j].ToString();
worksheet.Cells[j + 1][i + 2] = data;
}
}
worksheet.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.ActiveWorkbook.Close(false, null, null); // 关闭 Excel 文件且不保存
app.Quit();//退出excel
}
}
if (leixing == "降水成果_xz")
{
FileInfo fi = new FileInfo(path);
if (fi.Exists)
{
for (int i = 2; i < tt.Rows.Count; i++)
{
//
for (int j = 2; j < tt.Columns.Count; j++)
{
//Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range(worksheet.Cells[i+1][j], Missing.Value);
//rng.Value2 = tt.Rows[i][j].ToString();
string data = tt.Rows[i][j].ToString();
worksheet.Cells[j + 1][i + 2] = data;
}
}
worksheet.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.ActiveWorkbook.Close(false, null, null); // 关闭 Excel 文件且不保存
app.Quit();//退出excel
}
}
}