1,工具类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using Aspose.Cells;
using System.Web;
namespace WebBrowserEx.Common
{
public static class ImportExcel
{
public static string ToExcel(this DataTable dt, string fullFileName)
{
Workbook workbook = new Workbook();
Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
//cellSheet.Cells[rowIndex, colIndex].SetStyle.Font.IsBold = true;
//cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 10;
StyleFlag styleFlag = new StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
workbook.Save(HttpContext.Current.Server.MapPath("/UploadFiles/"+fullFileName));
return "/UploadFiles/"+fullFileName ;
}
public static void WriteFile(string filePath)
{
var response = HttpContext.Current.Response;
try
{
filePath =HttpContext.Current.Server.MapPath(filePath);
if (File.Exists(filePath))
{
FileInfo info = new FileInfo(filePath);
long fileSize = info.Length;
response.Clear();
response.ContentType = "application/octet-stream";
response.AddHeader("Content-Disposition", "attachement;filename=" + System.Web.HttpContext.Current.Server.UrlEncode(info.FullName));
//指定文件大小
response.AddHeader("Content-Length", fileSize.ToString());
response.WriteFile(filePath, 0, fileSize);
response.Flush();
}
}
catch
{ }
finally
{
response.Close();
}
}
}
}
2,调用示例
var dt = new DataTable();
dt.Columns.Add("提交人",typeof(string));
dt.Columns.Add("公司名称", typeof(string));
dt.Columns.Add("号牌类型", typeof(string));
dt.Columns.Add("车牌号", typeof(string));
dt.Columns.Add("车辆类型", typeof(string));
dt.Columns.Add("身份证号码", typeof(string));
dt.Columns.Add("持有人", typeof(string));
dt.Columns.Add("电话", typeof(string));
dt.Columns.Add("核定载客数量", typeof(string));
dt.Columns.Add("识别代码", typeof(string));
dt.Columns.Add("发动机号码", typeof(string));
dt.Columns.Add("身份证类型", typeof(string));
dt.Columns.Add("地址", typeof(string));
dt.Columns.Add("有效期", typeof(string));
dt.Columns.Add("邮编", typeof(string));
dt.Columns.Add("品牌", typeof(string));
dt.Columns.Add("核定载质量", typeof(string));
dt.Columns.Add("车辆型号", typeof(string));
dt.Columns.Add("提交时间", typeof(string));
foreach (var item in data)
{
var dr = dt.NewRow();
dr["提交人"] = item.User.RealName;
dr["公司名称"] = item.User.CompanyName;
dr["号牌类型"] = item.PlateType.ToString();
dr["车牌号"] = item.CarNum;
dr["车辆类型"] = item.CartType;
dr["电话"] = item.Mobile;
dr["身份证号码"] = item.IdCardNum;
dr["持有人"] = item.Owner;
dr["核定载客数量"] = item.PassengerCount;
dr["识别代码"] = item.CarIdentificationCode;
dr["发动机号码"] = item.EngineNum;
dr["身份证类型"] = item.IdCartType;
dr["地址"] = item.Address;
dr["有效期"] = item.Yxq;
dr["邮编"] = item.PostCode;
dr["品牌"] = item.BrandName;
dr["核定载质量"] = item.Quality.ToString();
dr["车辆型号"] = item.CarModel;
dr["提交时间"] = item.AddTime.ToString("yyyy/MM/dd");
dt.Rows.Add(dr);
}
//var resutl=ImportExcel.start(dt);
var path= ImportExcel.ToExcel(dt,DateTime.Now.ToString("yyyyMMddHHmmss")+".xls");
if(path!=null){
ImportExcel.WriteFile(path);
Response.Write(Success("ok"));
}
3,说明导出excle是根据DataTable的列名作为第一行
4,引用dll下载地址https://download.csdn.net/download/weixin_41609327/10758899