excel导出的几种方式

 

DTCheckOut = Constant.GetPage("ckgreencountdj", "szlx,greennum,sj,bz,userbz", pageIndex1, tiaojian, " greenid desc ", "999", out count); 

protected void Button_checkout_Click(object sender, EventArgs e)
{
DataBand();
//DataTExcel(DTCheckOut);
ExportExcel(DTCheckOut);
}

 

1、

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

 

protected void ExportExcel(System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count == 0) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}

 

 

2、ExcelExporter excel = new ExcelExporter(ds);

 

protected void Button_checkout_Click(object sender, EventArgs e)
{
string TiaoJian = " ckid=" + ckid.ToString() + str_Where+" order by greenid desc" ;
System.Data.DataSet ds = Constant.ListPage(" where" + TiaoJian); //直接查询导出

ds.Tables[0].Columns["greenid"].ColumnName = "编号";
ds.Tables[0].Columns["szlx"].ColumnName = "收支";
ds.Tables[0].Columns["greennum"].ColumnName = "张数";
ds.Tables[0].Columns["sj"].ColumnName = "时间";
ds.Tables[0].Columns["bz"].ColumnName = "备注";
ds.Tables[0].Columns["userbz"].ColumnName = "用户备注";
string TimePath = Server.MapPath("../excel").ToString() + "\\";
if (!Directory.Exists(TimePath))
Directory.CreateDirectory(TimePath);

System.IO.DirectoryInfo path = new System.IO.DirectoryInfo(Server.MapPath("../excel"));

//foreach (System.IO.FileInfo f in path.GetFiles())
//{
// if (f.Name.ToString().IndexOf("绿单B账户明细") > 1)
// f.Create();
//}

string name = "绿单B账户明细" + DateTime.Now.ToString("yyyy-MM-dd");
ExcelExporter excel = new ExcelExporter(ds);
excel.Export(TimePath + name + ".xls");
Response.Redirect("../excel/" + name + ".xls?time=" + DateTime.Now.ToString());

}

 

3、using Microsoft.Office.Interop.Excel;

 

protected void ExportExcel(System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count == 0) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string[] ColumnName = new string[] { "序号", "收支", "张数", "时间", "备注", "用户备注" };
for (int i = 0; i < dt.Columns.Count; i++)
{
//worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
worksheet.Cells[1, i + 1] = ColumnName[i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;

}

xlApp.Visible = false;

//生成新的文件
string guid = Guid.NewGuid().ToString();
string strPath = Server.MapPath("~/exceleet" + guid + ".xls");
string Strdropdown = "~/exceleet" + guid + ".xls";
workbook.SaveAs(strPath, null, null, null, null, null, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//关闭原来的
workbook.Close(false, null, null);
workbook = null;

Response.Redirect(Strdropdown, false);
}

}