一些Excel的相关操作
//Excel导入到数据库（sql/oracle）和Grid/DataTable导出Excel
/// <summary>
/// 将Excel文件数据批量倒入到数据库—SQL
/// </summary>
private void BatchUploadSql()
{
string myString = "provider = microsoft.jet.oledb.4.0; data source = E:/Workaround/TestItem/BatchExcel/test.xls;extended properties=excel 8.0";
OleDbConnection cnnXls = new OleDbConnection(myString);
OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [sheet1$]", cnnXls);
DataSet myDs = new DataSet();
myAdapter.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = string.Empty;
/*注意：cnnString不能有空格*/
string cnnString = "provider=SqlOleDb;server=pwdpc;database=Northwind;uid=sa;pwd=123456Aa";
OleDbConnection conn = new OleDbConnection(cnnString);
conn.Open();
OleDbCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
strSql = string.Format("insert into Customers(CustomerID, CompanyName, ContactName) values ( ");
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "'" + ", ";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "'" + ", ";
strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "'" + ")";
try
{
myCmd = new OleDbCommand(strSql, conn);
myCmd.ExecuteNonQuery();
Response.Write(" ");
}
catch (Exception ex)
{
throw ex;
}
}
conn.Close();
}
}
/// <summary>
/// 将Excel文件数据批量倒入到数据库—ORACLE
/// </summary>
private void BatchExcelOracle()
{
if (System.IO.File.Exists(uploadFile.Value.ToString()))
{
string[] fileName = uploadFile.Value.ToString().Split(Convert.ToChar("."));
string fileType = fileName[fileName.Length - 1];
if (fileType.ToUpper() != "XLS")
{
Response.Write(" ");
}
else
{
string strConnUpload = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + uploadFile.Value.ToString().Replace("\\", "\\\\") + "; Extended Properties=Excel 8.0";
OleDbConnection connUpload = new OleDbConnection(strConnUpload);
connUpload.Open();
string strSQL = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter daUpload = new OleDbDataAdapter(strSQL, connUpload);
DataSet dsUpload = new DataSet();
daUpload.Fill(dsUpload, "[Sheet1$]");
/*注意:data source 对大小写敏感*/
string strConn = "data source=ORACLE;user id=pwd;password=123456Aa;Min Pool Size=1";
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
int isSuccess = 0;
int isFail = 0;
OracleCommand cmd = null;
for (int i = 0; i < dsUpload.Tables["[Sheet1$]"].Rows.Count; i++)
{
string tempStr = string.Format("insert into MYTABLE(ID, Name, Sex) values ( ");
tempStr += dsUpload.Tables[0].Rows[i].ItemArray[0].ToString() + ", ";
tempStr += "'" + dsUpload.Tables[0].Rows[i].ItemArray[1].ToString() + "'" + ", ";
tempStr += "'" + dsUpload.Tables[0].Rows[i].ItemArray[2].ToString() + "'" + ")";
try
{
cmd = new OracleCommand(tempStr, conn);
cmd.ExecuteNonQuery();
isSuccess++;
}
catch
{
isFail++;
}
}
string message = "全部上传成功";
if (isFail != 0)
{
message = "成功" + isSuccess + "条，失败" + isFail + "条。";
}
conn.Close();
connUpload.Close();
Response.Write(" ");
}
}
else
{
Response.Write(" ");
}
}
/// <summary>
/// 将Grid导出到Excel
/// </summary>
/// <param name="ctl"></param>
/// <param name="FileName"></param>
public static void ToExcel(System.Web.UI.Control ctl, string FileName)
{
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 将DataTable中的数据导出到指定的Excel文件中
/// </summary>
/// Web页面对象
/// 包含被导出数据的DataTable对象
/// Excel文件的名称
public static void Export(System.Web.UI.Page page, System.Data.DataTable dt, string FileName)
{
System.Web.HttpResponse httpResponse = page.Response;
System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
dataGrid.DataSource = dt.DefaultView;
dataGrid.AllowPaging = false;
dataGrid.HeaderStyle.BackColor = System.Drawing.Color.AliceBlue;
dataGrid.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.DataBind();
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
httpResponse.ContentEncoding = System.Text.Encoding.Default;
httpResponse.ContentType = "application/ms-excel";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
dataGrid.RenderControl(hw);
System.Web.HttpContext.Current.Response.Write(tw.ToString());
System.Web.HttpContext.Current.Response.End();
}
首先建立一个EXCEL的模板放在服务器上，当用户将数据导出到EXCEL的时候复制一份模版的副本，并将导出的数据写到这个Excel里，这样在服务器端就有一个供用户下载的纯正Excel的文件了。用户下载之后将数据填写完毕上传，首先上传到服务器，然后再去读取这个文件的数据，避免读客户端数据引发不必要的麻烦。
// ///
// /// 复制Excel模板并向Excel中写入数据
// ///
// ///
// private void WriteDataToExcel(DataTable dt)
// {
// int isSuccess = 0;
// int isFail = 0;
// try
// {
// //复制excel文件
// string TmpFileForDownLoad = UpLoadFilePath + Guid.NewGuid().ToString() + ".xls";//新生成的文件地址
// string TemplateFile = Server.MapPath("../BatchTemplate/"
// + ConfigInfo.GetConfigValue("TowerAdminAreaTmpName"));//用于复制的文件地址
// System.IO.File.Copy(TemplateFile, TmpFileForDownLoad, true);
// String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
// + TmpFileForDownLoad
// + ";Extended Properties=Excel 8.0;";
// //写入数据
// OleDbCommand cmd = new OleDbCommand(string.Empty, new OleDbConnection(strConn));
// cmd.Connection.Open();
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// try
// {
// cmd.CommandText = string.Format("insert into [Sheet1$] ({0},{1},{2},{3},{4}) " +
// "values('{5}', '{6}', '{7}', '{8}', '{9}')",
// "塔标识", "塔号", "区县", "乡镇", "村庄"
// , dt.Rows[i]["TOWER_ID"].ToString()
// , dt.Rows[i]["TOWER_CODE"].ToString()
// , dt.Rows[i]["COUNTY"].ToString()
// , dt.Rows[i]["TOWN"].ToString()
// , dt.Rows[i]["VILLAGE"].ToString());
// cmd.ExecuteNonQuery();
// isSuccess++;
// }
// catch
// {
// isFail++;
// }
// }
// cmd.Connection.Close();
// DownLoadExcel(TmpFileForDownLoad);
// }
// catch (Exception ex)
// {
// throw ex;
// }
// }
// ///
// /// 使用Excel对象下载Excel文件
// ///
// /// 服务器端文件路径
// public void DownLoad(string serverPath)
// {
// try
// {
// Excel.Application xlApp = new Excel.Application();
// object missing = System.Reflection.Missing.Value;
// Excel.Workbook xlBook = xlApp.Workbooks.Open(serverPath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
// Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// //下载到本地的文件路径及文件名
// string fileName = "c:/EXCEL对象下载的文件.xls";
// xlSheet.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing);
// if (fileName == string.Empty)
// return;
// Excel.Application NewApp = new Excel.Application();
// Excel.Workbook NewBook;
// Excel.Worksheet NewSheet;
// NewBook = NewApp.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
// NewSheet = (Excel.Worksheet)NewBook.Worksheets[1];
// NewApp.Visible = false;
// //关闭EXCEL工作台
// if (!NewApp.Visible)
// {
// object missing1 = System.Reflection.Missing.Value;
// xlBook.Close(missing1, missing1, missing1);
// NewBook.Close(missing1, missing1, missing1);
// }
// Response.Write(" ");
// }
// catch (Exception ex)
// {
// Response.Write(" ");
// }
// }
// ///
// /// 使用WebClient方式下载Excel文件
// ///
// private void DownLoad1(string serverPath)
// {
// System.Net.WebClient wc = new System.Net.WebClient();
// wc.DownloadFile(serverPath, @"c:\WebClient下载的文件.xls");
// }
// ///
// /// 使用输出流的方式下载Excel文件
// ///
// private void DownLoad2(string serverPath)
// {
// System.IO.FileInfo file = new System.IO.FileInfo(serverPath);
// Response.Clear();
// Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
// Response.AddHeader("Content-Length", file.Length.ToString());
// Response.ContentType = "application/octet-stream";
// //或动态产生数据后写入Response.OutputStream
// Response.WriteFile(file.FullName);
// Response.End();
// }
/////
// /// 将Excel文件数据批量倒入到数据库
// ///
// private void BatchExcelOracle(HtmlInputFile hifFile)
// {
// string clientPath = hifFile.Value;
// string[] fileName = clientPath.Split(Convert.ToChar("."));
// string fileType = fileName[fileName.Length - 1];
// if (fileType.ToUpper() != "XLS")
// {
// string message = "只能录入数据源只能是XLS格式的文件！";
// string url = string.Format("TowerAdminArea_List.aspx?Operation=ReadOnly") + UrlForNextPage();
// CommonMethod.RedirectPage(message, url, 0);
// }
// else
// {
// string NewFileName = UpLoadFilePath + Guid.NewGuid().ToString() + ".xls";//新生成的文件地址
// //上传文件
// hifFile.PostedFile.SaveAs(NewFileName);
// string strConnUpload = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + NewFileName + "; Extended Properties=Excel 8.0";
// OleDbConnection connUpload = new OleDbConnection(strConnUpload);
// connUpload.Open();
// string strSQL = "SELECT * FROM [Sheet1$]";
// OleDbDataAdapter daUpload = new OleDbDataAdapter(strSQL, connUpload);
// DataSet dsUpload = new DataSet();
// daUpload.Fill(dsUpload, "[Sheet1$]");
// int isSuccess = 0;
// int isFail = 0;
// for (int i = 0; i < dsUpload.Tables["[Sheet1$]"].Rows.Count; i++)
// {
// int towerID = Convert.ToInt32(dsUpload.Tables[0].Rows[i].ItemArray[0]);
// Tower towerObj = new Tower(towerID);
// if (towerObj.ProjectID != _mProjectId)
// {
// isFail++;
// break;
// }
// towerObj.County = dsUpload.Tables[0].Rows[i].ItemArray[2].ToString().Trim();
// towerObj.Town = dsUpload.Tables[0].Rows[i].ItemArray[3].ToString().Trim();
// towerObj.Village = dsUpload.Tables[0].Rows[i].ItemArray[4].ToString().Trim();
// try
// {
// towerObj.Update();
// isSuccess++;
// }
// catch (Exception ex)
// {
// throw ex;
// isFail++;
// }
// }
// string message = "全部上传成功，共" + isSuccess + "条。";
// if (isFail != 0)
// {
// message = "成功" + isSuccess + "条，失败" + isFail + "条。";
// }
// connUpload.Close();
// string url = string.Format("TowerAdminArea_List.aspx?Operation=ReadOnly") + UrlForNextPage();
// CommonMethod.RedirectPage(message, url, 0);
// }
// }
