asp.net 大量和数据导出到Excel 服务器不用安装office(sql语句创建)
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Data.OleDb;
/// <summary>
///DatatoExcel 的摘要说明
/// </summary>
public class DatatoExcel
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
public DatatoExcel()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
public string DataTableToExcel(DataTable dt, string ExcelPaht)
{
if (dt == null)
{
return "没有数据导出！";
}
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
StringBuilder sb;
string connString;
if (rows == 0)
{
return "没有数据！";
}
sb = new StringBuilder();
connString = string.Format(ConnectionString,ExcelPaht);
//生成创建表的脚本
sb.Append("Create table");
sb.Append(" 成员 "+"(");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append(string.Format("{0} varchar", dt.Columns[i].ColumnName));
}
else
{
sb.Append(string.Format("{0} varchar",dt.Columns[i].ColumnName));
}
sb.Append(")");
}
using (OleDbConnection objCon=new OleDbConnection(connString))
{
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objCon;
objCmd.CommandText = sb.ToString();
try
{
objCon.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
return "在Excel中创建表失败，错误信息：" + e.Message;
}
//生成插入数据脚本
#region 生成插入数据脚本
sb.Remove(0,sb.Length);
//for (int j = 0; j < cols; j++)
//{
// if (j < cols - 1)
// {
// sb.Append(dt.Columns[j].ColumnName+",");
// }
// else
// {
// sb.Append(dt.Columns[j].ColumnName+") values (");
// }
//}
for (int l = 0; l < dt.Rows.Count; l++)
{
//if (l < cols - 1)
//{
// sb.Append("@" + dt.Columns[l].ColumnName + ",");
//}
//else
//{
// sb.Append("@"+dt.Columns[l].ColumnName+")");
//}
//sb.Append("INSERT INTO");
sb.Append("INSERT INTO 成员 (易阳指号码) values" + "("+dt.Rows[l]["易阳指号码"]+")");
objCmd.CommandText = sb.ToString();
objCmd.ExecuteNonQuery();
sb.Remove(0,sb.Length);
}
objCon.Close();
#endregion
//建立插入动作的Command
//objCmd.CommandText = sb.ToString();
//OleDbParameterCollection param =objCmd.Parameters;
//for (int m = 0; m < cols; m++)
//{
// param.Add(new OleDbParameter("@"+dt.Columns[m].ColumnName,OleDbType.VarChar));
//}
////遍历DataTable将数据插入新建的Excel文件中
//foreach (DataRow row in dt.Rows)
//{
// for (int n = 0; n < param.Count; n++)
// {
// param.AddWithValue("@"+dt.Columns[n].ColumnName,row);
// }
// objCmd.ExecuteNonQuery();
//}
return "数据已成功导入！";
}
}
}
.cs调用
/// <summary>
/// 导出Excel事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void lbtnExcel_Click(object sender, EventArgs e)
{
// ExportExcel(db.GetRecord(strSQL));
DatatoExcel de = new DatatoExcel();
//ClientScript.RegisterStartupScript(this.GetType(), "", "alert('" + de.DataTableToExcel(db.GetRecord(strSQL), "C:\" + Id + ".xls") + "')", true);
TableToExcel();
}
public void TableToExcel()
{
string pathExcel = Server.MapPath("Data/");
DatatoExcel Excel = new DatatoExcel();
int page = anpExportExcel.PageCount; //总页数
if (page == 1)
{
BinData();
if (dtExcel.Rows.Count > 0)
{
Excel.DataTableToExcel(dtExcel, pathExcel + Id + ".xls");
//****************下载******************
System.IO.FileInfo file = new System.IO.FileInfo(pathExcel + Id + ".xls");
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//下载文件默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Id + ".xls"));
//添加头信息，指定文件大小，让浏览器能显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/ms-excel";
//把文件发送该客户段
Response.WriteFile(file.FullName);
Response.Flush();
Response.Clear();
}
else
{
ClientScript.RegisterStartupScript(this.GetType(),"","alert('没有要导出的数据！');",true);
}
}
else
{
for (int i = 1; i < page; i++)
{
anpExportExcel.CurrentPageIndex = i;
BinData();
Excel.DataTableToExcel(dtExcel,pathExcel+Id+GetName(i)+".xls");
}
if (dtExcel.Rows.Count > 0)
{
//*************下载***************
string pa = Server.MapPath("Data/" + Id + ".zip");
string pathDa = Server.MapPath("Export/");
string path = Server.MapPath("Data/");
// UnpackFiles(name, pa);
//zipFile(path, Id + ".rar");
ZipDir(path, pathDa, 2, Id);
System.IO.FileInfo file = new System.IO.FileInfo(Server.MapPath("Export/" + Id + ".zip"));
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//下载文件默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Id + ".zip"));
//添加头信息，指定文件大小，让浏览器能显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/zip";
//把文件发送该客户段
Response.WriteFile(file.FullName);
Response.Flush();
Response.Clear();
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('没有要导出的数据！');", true);
}
}
////*********下载后删除文件********
string pathDel = Server.MapPath("Export/");
string Delpath = Server.MapPath("Data/");
if (Directory.Exists(pathDel))
{
foreach (string d in Directory.GetFileSystemEntries(pathDel))
{
if (File.Exists(d))
{ File.Delete(d); }
else
{
}
// DeleteFolder();
}
// Directory.Delete(Delpath, true);
}
if (Directory.Exists(Delpath))
{
foreach (string d in Directory.GetFileSystemEntries(Delpath))
{
if (File.Exists(d))
{ File.Delete(d); }
else
{
}
// DeleteFolder();
}
//Directory.Delete(Delpath, true);
}
}
/// <summary>
/// 压缩文件夹
/// </summary>
/// <param name="DirToZip">文件夹路径</param>
/// <param name="ZipedFile">输出文件路径</param>
/// <param name="CompressionLevel">设置缓存大小</param>
///<param name="fileName">压缩后的文件名称</param>
public static void ZipDir(string DirToZip, string ZipedFile, int CompressionLevel, string fileName)
{
//压缩文件为空时默认与压缩文件夹同一级目录
if (ZipedFile == string.Empty)
{
ZipedFile = DirToZip.Substring(DirToZip.LastIndexOf("\") + 1);
ZipedFile = DirToZip.Substring(0, DirToZip.LastIndexOf("\")) + "\" + ZipedFile + ".zip";
}
if (System.IO.Path.GetExtension(ZipedFile) != ".zip")
{
ZipedFile = ZipedFile + fileName + ".zip";
}
using (ZipOutputStream zipoutputstream = new ZipOutputStream(System.IO.File.Create(ZipedFile)))
{
zipoutputstream.SetLevel(CompressionLevel);
Crc32 crc = new Crc32();
System.IO.DirectoryInfo myDir = new DirectoryInfo(DirToZip);
List<DictionaryEntry> fileList = GetAllFiles(DirToZip);
foreach (DictionaryEntry item in fileList)
{
System.IO.FileStream fs = System.IO.File.OpenRead(item.Key.ToString());
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
ZipEntry entry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length + 1));
entry.DateTime = (DateTime)item.Value;
entry.Size = fs.Length;
fs.Close();
crc.Reset();
crc.Update(buffer);
entry.Crc = crc.Value;
zipoutputstream.PutNextEntry(entry);
zipoutputstream.Write(buffer, 0, buffer.Length);
}
}
}
/// <summary>
/// 获取所有文件
/// </summary>
/// <returns></returns>
private static List<DictionaryEntry> GetAllFiles(string dir)
{
List<DictionaryEntry> dictonary = new List<DictionaryEntry>();
if (!System.IO.Directory.Exists(dir))
{
return dictonary;
}
else
{
System.IO.DirectoryInfo root = new System.IO.DirectoryInfo(dir);
System.IO.FileSystemInfo[] arrary = root.GetFileSystemInfos();
for (int i = 0; i < arrary.Length; i++)
{
dictonary.Add(new DictionaryEntry(arrary[i].FullName, arrary[i].LastWriteTime));
}
}
return dictonary;
}
public string GetName(int num)
{
if (num == 1)
{
return "A";
}
else if (num == 2)
{
return "B";
}
else if (num == 3)
{
return "C";
}
else if (num == 4)
{
return "D";
}
else if (num == 5)
{
return "E";
}
else if (num == 6)
{
return "F";
}
else if (num == 7)
{
return "G";
}
else
{
return "H";
}
}
浙公网安备 33010602011771号