c#操作Excel保存到Datatable方法
例子1
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。
NPOI下载地址：http://npoi.codeplex.com/
NPOI 读取excel 存入 Datatable，简易代码：
public DataTable Upload(string path)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read)) //打开xls文件
{
string fileExt = System.IO.Path.GetExtension(path);
IWorkbook wk;
if (fileExt == ".xls")
{
//excel 03
wk = new HSSFWorkbook(fs);
}
else if (fileExt == ".xlsx")
{
//excel 07
wk = new XSSFWorkbook(fs);
}
else
{
return null;
}
ISheet sheet = wk.GetSheetAt(0); //读取第一个Sheel表数据
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
if (dt.Columns.Count < i + 1)
{
string colname = Convert.ToChar(((int)'A') + i).ToString();//有局限最多到Z列
dt.Columns.Add(colname);
//lable1.Content = colname;
dr[i] = cell.ToString();
}
dr[i] = cell.ToString();
}
}
//dt = ImportDt(sheet,0,true);
dt.Rows.Add(dr);
}
wk = null;
sheet = null;
return dt;
}
}
三种读取Excel方法
---------------------------------------------------方法一
///
/// 解析Excel，返回DataTable
///
///
///
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
dt = LoadDataFromExcel(filePath, bookName).Tables[0];
//for (int i = 1; i < rows + 1; i++)
//{
// DataRow dr = dt.NewRow();
// for (int j = 1; j <= columns; j++)
// {
// _Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
// range.Select();
// if (i == 1)
// dt.Columns.Add("Columns" + j);// dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名，单合并单元格后，读取出来后列名相同会报错，所以最好不用
// dr[j - 1] = app.ActiveCell.Text.ToString();
// }
// dt.Rows.Add(dr);
//}
//newdt = dt.Clone();
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (dt.Rows[i][5].ToString() != "" && dt.Rows[i][6].ToString() != "" && dt.Rows[i][7].ToString() != "" && dt.Rows[i][8].ToString() != "" || i == 0)
// newdt.ImportRow(dt.Rows[i]);
//}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("数据绑定Excel失败! 失败原因："+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
}
----------------------------------------------------方法二
///
/// 解析Excel
///
///
///
///
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}——————————————————插入Excel
///
/// 写入Excel文档
///
/// 文件名称
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
for(int i=0;i
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
}
//新增、修改
_Excel.Application app = new _Excel.ApplicationClass();
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(UpdateExcelPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.Cells[i + 2, 1] = dt.Rows[i][0].ToString();
sheet.Cells[i + 2, 2] = dt.Rows[i][1].ToString();
}
book.Save();
book.Close(sheet, UpdateExcelPath, System.Type.Missing);
app.Quit();
System.GC.Collect();
——————————————————修改Excel的值
//修改第一行Name的值为张三
string strComm = "update [Sheet1$] set Name='张三' WHERE 工号='132'";
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbCommand com = new OleDbCommand(strComm, myConn);
com.ExecuteNonQuery();
myConn.Close();
