Excel操作
导出Excel添加语句
Response.Write("<style>td{mso-number-format:\"\\@\";}</style>");
protected void ToExcel(string fileName,string ehtm)
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString();
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");
Response.Write("<style>td{mso-number-format:\"\\@\";}</style>");
Response.Write(ehtm);
Response.End();
}
实现由Excel数据导入Access数据库
protected void Button1_Click(object sender, EventArgs e)
{
string strcon = System.Configuration.ConfigurationManager.ConnectionStrings["accessCon"].ConnectionString;
string sql = "select top 1 货运公司编号,货运公司名称,电话 from 货运公司 order by 货运公司编号 desc";
OleDbConnection con=new OleDbConnection(strcon);
OleDbCommand com=new OleDbCommand(sql,con);
OleDbTransaction tran=con.BeginTransaction();
com.Transaction=tran;
OleDbDataAdapter da = new OleDbDataAdapter(com);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
int curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
DataTable tb = this.getExcelDate();
for (int i = 0; i < tb.Rows.Count; i++)
{
DataRow dr = ds.Tables[0].NewRow();
dr[0] = ++curIndex;
dr[1] = tb.Rows[i][0];
dr[2] = tb.Rows[i][1];
ds.Tables[0].Rows.Add(dr);
}
try
{
da.Update(ds);
tran.Commint();
}
catch
{
tran.Roolback();
}
}
public DataTable getExcelDate()
{
string strExcelFileName = "D:\\货运公司.xls";
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
string sql = "select * from [货运公司$]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, strcon);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
另:C# 读取Excel文件的通用方法
private void Form1_Load(object sender, System.EventArgs e)
{
DataTable myT=ExcelToDataTable("D:/files.xls","sheet1");
}
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
//HDR和IMEX也一定要配合使用,IMEX=1应该是将所有的列全部视为文本,HDR:YES第一行作为列名,NO表示第一行不为列名
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
//string strExcel = "select * from [sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
return ds.Tables[strSheetName];
}