excel 处理方法
2024-10-11 16:29:16
//.方法一:采用OleDB读取EXCEL文件: //打开excel 返回指定表中的所有数据
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel = "";
System.Data.OleDb.OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}
//对于EXCEL中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到
public DataTable ExcelToDT(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[][].ToString().Trim();
return schemaTable;
} //写入excel
public void DSToExcel(string Path, DataSet oldds)
{ //先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "select * from [Sheet1$]";
myConn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix = "["; //获取insert语句中保留字符(起始位置)
builder.QuoteSuffix = "]"; //获取insert语句中保留字符(结束位置)
DataSet newds = new DataSet();
myCommand.Fill(newds, "Table1");
for (int i = ; i < oldds.Tables[].Rows.Count; i++)
{
//在这里不能使用ImportRow方法将一行导入到news中,因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
// 在使用ImportRow后newds内有值,但不能更新到Excel中因为所有导入行的DataRowState != Added
DataRow nrow = newds.Tables["Table1"].NewRow();
for (int j = ; j < newds.Tables[].Columns.Count; j++)
{
nrow[j] = oldds.Tables[].Rows[i][j];
}
newds.Tables["Table1"].Rows.Add(nrow);
}
myCommand.Update(newds, "Table1");
myConn.Close();
}
最新文章
- cosbench 异常 FreeMarker template error: The following has evaluated to null or missing
- Linux 的文本处理命令一览
- 【并查集】【树】最近公共祖先LCA-Tarjan算法
- 发测试 HTML/FILE/MYSQL/动态 20151120
- Carcraft
- 【转】arcgis server site 快速恢复与重建
- PIVOT 用于将列值旋转为列名
- SSIS包配置动态配置数据库连接
- c#高效的线程安全队列ConcurrentQueue<;T>;(上)
- 错误解决:release&#39; is unavailable: not available in automatic reference counting mode
- Arcgis 10.1中空间连接功能
- RHCA442学习笔记-Unit13网络性能调整
- Codeforce 216 div2
- HDU 5775 Bubble Sort(线段树)(2016 Multi-University Training Contest 4 1012)
- smarty获取变量的两种方式
- 《Netty5.0架构剖析和源码解读》【PDF】下载
- python github
- ambari2.6.1汉化记录
- CentOS-7修改主机名
- Android原生和H5交互;Android和H5混合开发;WebView点击H5界面跳转到Android原生界面。