using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
namespace SSCMS.CommonClass
{
public class ExcelHelper
{
#region 导出数据到Excel
public static string ExportDataToExcel(DataTable dt, string filePath, string fileName)
{
string syspath = System.Web.HttpContext.Current.Server.MapPath(filePath);
if (!System.IO.Directory.Exists(syspath))
{
Directory.CreateDirectory(syspath);
}
return ExportDataToExcel(dt, syspath, filePath, fileName);
}
#endregion
#region 具体操作(将数据表导出到Excel并输出)
public static string ExportDataToExcel(DataTable oldds, string FilePath, string FilePathOld, string FileName)
{
string ExcelResult = "";
try
{
string nowDay = DateTime.Now.ToString("yyyy-MM-dd");
string nowTime = DateTime.Now.ToString("HHmmss");
//对Excel文件重命名
string FileNameNow = FileName + "_" + nowTime + ".xls";
FilePath = FilePath.Replace('\\', '/');
if (FilePath.Substring(FilePath.Length - 1, 1) == "/")
{
FilePath = FilePath.Substring(0, FilePath.Length - 1);
}
string FilePathNow = FilePath + "/" + nowDay + "/"; // 新的文件夹地址
if (!System.IO.Directory.Exists(FilePathNow)) // 如果文件夹不存在则创建文件夹
{
System.IO.Directory.CreateDirectory(FilePathNow);
}
string FileFullPath = FilePathNow + FileNameNow; //文件全路径
//此处可不做判断,因为重新使用当前时间命名,文件一般不会重名
if (System.IO.File.Exists(FileFullPath))
{
System.IO.File.Delete(FileFullPath);
}
//先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + FileFullPath + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string _sql = "";
foreach (DataColumn _col in oldds.Columns)
{
if (_sql == "")
{
_sql = " CREATE TABLE [Sheet1] ( "
+ " [" + _col.ColumnName + "] Text(255) ";
}
else
{
_sql += ", [" + _col.ColumnName + "] Text(255) ";
}
}
_sql += ")";
Console.Write(_sql);
OleDbCommand _cmd = new OleDbCommand(_sql, myConn);
myConn.Open();
_cmd.ExecuteNonQuery();
myConn.Close();
string strCom = "select * from [Sheet1]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix = "["; //获取insert语句中保留字符(起始位置)
builder.QuoteSuffix = "]"; //获取insert语句中保留字符(结束位置)
DataSet newds = new DataSet();
myCommand.Fill(newds, "Table1");
for (int i = 0; i <= oldds.Rows.Count - 1; i++)
{
//在这里不能使用ImportRow方法将一行导入到news中,因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值,但不能更新到Excel中因为所有导入行的DataRowState!=Added
DataRow nrow = newds.Tables["Table1"].NewRow();
for (int j = 0; j <= oldds.Columns.Count - 1; j++)
{
string a = oldds.Rows[i][j].ToString();
nrow[j] = oldds.Rows[i][j];
}
newds.Tables["Table1"].Rows.Add(nrow);
}
myCommand.Update(newds, "Table1");
myConn.Close();
string FilePathForServer = FilePathOld + "/" + nowDay + "/" + FileNameNow;
FilePathForServer = FilePathForServer.Replace("//", "/");
FilePathForServer = FilePathForServer.Replace("/\\", "/");
ExcelResult = "数据导出成功,Excel文件保存的位置为:<a href='" + FilePathForServer + "'>点击下载文件</a>";
}
catch (Exception E)
{
ExcelResult = (E.ToString());
}
return ExcelResult;
}
#endregion
#region 直接导出Excel到客户端
public static void CreateExcel(DataTable dt, Page page, string FileName)
{
HttpResponse resp;
resp = page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
resp.ContentType = "application/vnd.ms-excel";//type=excel
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
#endregion
}
}