方法1：微软推荐服务器需安装Excel型
依赖：
软件：Office Excel 2007-2013
引用：Microsoft Office 14.0 Object Library
1.1 数据准备
//Excel文件名称
string ExcelName = System.DateTime.Now.ToString("yyMMdd") + "退款结算单.xlsx"; /// <summary>
/// 批量退款
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet(); DataTable dt1 = new DataTable();
dt1.Columns.Add("批次号");
dt1.Columns.Add("总金额(元)");
dt1.Columns.Add("总笔数"); DataRow row1 = dt1.NewRow(); row1 = dt1.NewRow();
row1["批次号"] = "";
row1["总金额(元)"] = "0.03";
row1["总笔数"] = "";
dt1.Rows.Add(row1); ds.Tables.Add(dt1); DataTable dt2 = new DataTable();
dt2.Columns.Add("商户订单号");
dt2.Columns.Add("支付宝交易号");
dt2.Columns.Add("退款金额");
dt2.Columns.Add("退款备注"); DataRow row2 = dt2.NewRow(); row2 = dt2.NewRow();
row2["商户订单号"] = "D150418092109-40";
row2["支付宝交易号"] = "";
row2["退款金额"] = "0.02";
row2["退款备注"] = "导入excel测试";
dt2.Rows.Add(row2); ds.Tables.Add(dt2); doExport(ds, Server.MapPath(ExcelName));
}
1.2 数据写入
private Microsoft.Office.Interop.Excel.Application _Excel = null;
/// <summary>
/// 将 DataSet 数据写入 _Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="strExcelFileName"></param>
private void doExport(DataSet ds, string strExcelFileName)
{
int rowIndex = ;//当前行数
int colIndex = ;//当前列数
_Excel = new Microsoft.Office.Interop.Excel.Application();
_Excel.Application.Workbooks.Add(true);
for (int i = ; i < ds.Tables.Count; i++)
{
DataTable table = ds.Tables[i];
colIndex = ;//列初始化
// 列标题
foreach (DataColumn col in table.Columns)
{
colIndex++;
_Excel.Cells[rowIndex, colIndex] = col.ColumnName;
}
// 内容
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = ;
foreach (DataColumn col in table.Columns)
{
colIndex++;
_Excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
}
}
rowIndex++;//行+1 防止table又多个情况
}
_Excel.Visible = false;
// 保存文件
SaveToDisk(strExcelFileName);
_Excel.Quit();
_Excel = null;
}
1.3 数据导出
/// <summary>
/// 导出方法
/// </summary>
/// <param name="path">路径</param>
private void SaveToDisk(string path)
{
if (!string.IsNullOrEmpty(path))
{
System.IO.FileInfo info = new FileInfo(path);
if (!info.Exists)
{
_Excel.ActiveWorkbook.SaveCopyAs(path); /*
微软为Response对象提供了一个新的方法TransmitFile来解决使用Response.BinaryWrite
下载超过400mb的文件时导致Aspnet_wp.exe进程回收而无法成功下载的问题。
代码如下：
*/
Response.ContentType = "text/xml";
Response.AddHeader("Content-Disposition", "attachment;filename=" + ExcelName);
string filename = Server.MapPath(ExcelName);
Response.TransmitFile(filename);
}
else
{
info.Delete();
_Excel.DisplayAlerts = false;
_Excel.AlertBeforeOverwriting = false;
_Excel.ActiveWorkbook.SaveCopyAs(path);
}
}
}
方法2：第三方组件NPOI（2.0版本后受到微软支持）
依赖：
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll
NPOI下载地址 - http://npoi.codeplex.com/releases
2.1 简单例子：
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel; //创建全新的Workbook
IWorkbook workbook = new XSSFWorkbook(); //创建Sheet
workbook.CreateSheet("Sheet1"); //写入一个简单日期 并设置格式
ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow().CreateCell();
cell.SetCellValue(new DateTime(,,));
//set date format
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
IDataFormat format = hssfworkbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy年m月d日");
cell.CellStyle=cellStyle; //保存Workbook
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
这样就好了。
2.2 复杂例子（包含下载功能）
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="ExcelName">文件名称</param>
public void NPOIExcel(DataSet ds, string ExcelName)
{
int row_index = 0;
//创建全新的Workbook
IWorkbook workbook = new XSSFWorkbook(); //创建Sheet
workbook.CreateSheet("Sheet1"); //根据Sheet名字获得Sheet对象
ISheet sheet = workbook.GetSheet("Sheet1");
IRow row; row = sheet.CreateRow(row_index);
for (int i = 0; i < ds.Tables.Count; i++)
{
//写入标题
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(ds.Tables[i].Columns[j].Caption.ToString()); }
row = sheet.CreateRow(++row_index); //写入数据
foreach (DataRow r in ds.Tables[i].Select())
{
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(r[j].ToString());
} row = sheet.CreateRow(++row_index);
} } //保存Workbook方式一： 以文件形式保存到服务器中（每次导出都会生成一个文件，慎重使用）
FileStream sw = File.Create(Server.MapPath("file/" +ExcelName));
workbook.Write(sw);
sw.Close(); //保存Workbook方式二： 保存到内存流中
var stream = new MemoryStream();
workbook.Write(stream); //文件下载
Response.Clear();
Response.Charset = "utf-8";
Response.Buffer = true;
this.EnableViewState = false;
Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename="+ExcelName); string filename =Server.MapPath("file/" + ExcelName);//通过服务器文件下载
Response.WriteFile(filename);
Response.BinaryWrite(StreamToBytes(stream));//通过内存流下载，StreamToBytes为Stream转byte[] 方法 可查看我其他随笔有
Response.Flush(); Response.Close();
Response.End();
}