print?#region 導出Excel
/// <summary>
/// 導出Excel
/// </summary>
/// <param name="page">請求的頁面this</param>
/// <param name="dataTable">導出的數據源</param>
/// <param name="fileName">保存文件名稱</param>
/// <returns>布爾值</returns>
public bool ExportExcel(Page page, DataTable dataTable, string fileName)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";
page.EnableViewState = false;
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//設置輸出流為簡體中文
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//輸出列名
for (int i = 0; i < dataTable.Columns.Count; i++)
HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t");
HttpContext.Current.Response.Write("\r\n");
//輸出數據
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t");
}
HttpContext.Current.Response.Write("\r\n");
}
//輸出當前緩存內容
//HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
return true;
}
catch
{
return false;
}
}
#endregion
#region 導出Excel
/// <summary>
/// 導出Excel
/// </summary>
/// <param name="page">請求的頁面this</param>
/// <param name="dataTable">導出的數據源</param>
/// <param name="fileName">保存文件名稱</param>
/// <returns>布爾值</returns>
public bool ExportExcel(Page page, DataTable dataTable, string fileName)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";
page.EnableViewState = false;
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//設置輸出流為簡體中文
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//輸出列名
for (int i = 0; i < dataTable.Columns.Count; i++)
HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t");
HttpContext.Current.Response.Write("\r\n");
//輸出數據
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t");
}
HttpContext.Current.Response.Write("\r\n");
}
//輸出當前緩存內容
//HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
return true;
}
catch
{
return false;
}
}
#endregion
[csharp] view plaincopyprint?#region 導出Excel 自定義格式
/// <summary>
/// 導出Excel
/// 1.文本：vnd.ms-excel.numberformat:@
/// 2.日期：vnd.ms-excel.numberformat:yyyy/mm/dd
/// 3.數字：vnd.ms-excel.numberformat:#,##0.00
/// 4.貨幣：vnd.ms-excel.numberformat:￥#,##0.00
/// 5.百分比：vnd.ms-excel.numberformat: #0.00%
/// </summary>
/// <param name="fileName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public bool Export(string fileName, DataTable dt)
{
try
{
HttpResponse resp;
resp = System.Web.HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
resp.AppendHeader("Content-Type", "application/ms-excel");
StringBuilder colHeaders = new StringBuilder();
StringBuilder ls_item = new StringBuilder();
DataRow[] myRow = dt.Select();
int cl = dt.Columns.Count;
colHeaders.Append(" <html><head> \n ");
colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n ");
colHeaders.Append(" </head> \n ");
colHeaders.Append(" <body> \n ");
colHeaders.Append(" <table border='1'> ");
colHeaders.Append(" <tr> ");
//輸出列名
for (int i = 0; i < dt.Columns.Count; i++)
colHeaders.Append("<td style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>");
colHeaders.Append("</tr> ");
resp.Write(colHeaders.ToString());
foreach (DataRow row in myRow)
{
ls_item.Append("<tr>");
for (int i = 0; i < cl; i++)
{
if (i == (cl - 1))
{
ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n");
}
else
{
ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>");
}
}
ls_item.Append("</tr>");
}
ls_item.Append(" </table> \n ");
ls_item.Append(" </body> \n ");
ls_item.Append(" </html>");
resp.Write(ls_item.ToString());
resp.End();
return true;
}
catch
{
return false;
}
}
#endregion
#region 導出Excel 自定義格式
/// <summary>
/// 導出Excel
/// 1.文本：vnd.ms-excel.numberformat:@
/// 2.日期：vnd.ms-excel.numberformat:yyyy/mm/dd
/// 3.數字：vnd.ms-excel.numberformat:#,##0.00
/// 4.貨幣：vnd.ms-excel.numberformat:￥#,##0.00
/// 5.百分比：vnd.ms-excel.numberformat: #0.00%
/// </summary>
/// <param name="fileName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public bool Export(string fileName, DataTable dt)
{
try
{
HttpResponse resp;
resp = System.Web.HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
resp.AppendHeader("Content-Type", "application/ms-excel");
StringBuilder colHeaders = new StringBuilder();
StringBuilder ls_item = new StringBuilder();
DataRow[] myRow = dt.Select();
int cl = dt.Columns.Count;
colHeaders.Append(" <html><head> \n ");
colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n ");
colHeaders.Append(" </head> \n ");
colHeaders.Append(" <body> \n ");
colHeaders.Append(" <table border='1'> ");
colHeaders.Append(" <tr> ");
//輸出列名
for (int i = 0; i < dt.Columns.Count; i++)
colHeaders.Append("<td style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>");
colHeaders.Append("</tr> ");
resp.Write(colHeaders.ToString());
foreach (DataRow row in myRow)
{
ls_item.Append("<tr>");
for (int i = 0; i < cl; i++)
{
if (i == (cl - 1))
{
ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n");
}
else
{
ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>");
}
}
ls_item.Append("</tr>");
}
ls_item.Append(" </table> \n ");
ls_item.Append(" </body> \n ");
ls_item.Append(" </html>");
resp.Write(ls_item.ToString());
resp.End();
return true;
}
catch
{
return false;
}
}
#endregion
[csharp]
#region 導入Excel
public string ImportExcel(string[] list, string filePath)
{
string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension獲得文件的擴展名
if (isXls != ".xls")
return "請選擇Excel文件導入！";
DataSet ds = ExecleDataSet(filePath);//調用自定義方法
DataRow[] dr = ds.Tables[0].Select();//定義一個DataRow數組
int rowsnum = ds.Tables[0].Rows.Count;
if (ds.Tables[0].Rows.Count == 0)
return "Excel無數據！";
return "";
}
//OleDB連接讀取Excel中數據
public DataSet ExecleDataSet(string filePath)
{
string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(OleDbConnection);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds);
conn.Close();
return ds;
}
#endregion
#region 導入Excel
public string ImportExcel(string[] list, string filePath)
{
string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension獲得文件的擴展名
if (isXls != ".xls")
return "請選擇Excel文件導入！";
DataSet ds = ExecleDataSet(filePath);//調用自定義方法
DataRow[] dr = ds.Tables[0].Select();//定義一個DataRow數組
int rowsnum = ds.Tables[0].Rows.Count;
if (ds.Tables[0].Rows.Count == 0)
return "Excel無數據！";
return "";
}
//OleDB連接讀取Excel中數據
public DataSet ExecleDataSet(string filePath)
{
string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(OleDbConnection);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds);
conn.Close();
return ds;
}
#endregion
