目前常用的两种方法：
1.组织数据向客户端发送文件流。（优点：不在Server上生成多余的文件。缺点:IE能下载，迅雷下载有问题。FireFox文件格式不明显，但也能打开）
2.在Server端生成Excel文件，然后重定向到这个文件地址。（优点：1、的全部缺点都能克服。缺点：要在Server上生成物理文件）
方法一、
public static void ExportDsToXls(Page page, string fileName, DataSet ds, List<int> list)
{
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";
//page.Response.Charset = "UTF-8";
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
page.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
page.EnableViewState = false;
page.Response.Write(ExportTable(ds, list.ToArray()));
page.Response.End();
}
public static string ExportTable(DataSet ds, int[] list)
{
string data = "";
//data = ds.DataSetName + "\n";
foreach (DataTable tb in ds.Tables)
{
//data += tb.TableName + "\n";
data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
//写出列名
data += "<tr style=\"font-weight: bold; \">";
int count = 1;
int initNum = 4;//前四列不动
int baseInit = initNum;
int initI = 0;
foreach (DataColumn column in tb.Columns)
{
if (count > initNum)
{
for (int i = initI; i < list.Length; i++)
{
int ins = list[i];
if (baseInit < count && (baseInit + ins) >= count)
{
string color = i % 2 == 0 ? "#CCCC66" : "#3399CC";
data += String.Format("<td align='center' height='90' style='width:100px;WORD-BREAK: break-all; ' bgcolor='{1}'>{0}</td>", column.ColumnName, color);
count++;
break;
}
else
{
baseInit += ins;
initI++;
continue;
}
}
}
else
{
data += "<td align='center' style='width:100px;'>" + column.ColumnName + "</td>";
count++;
}
}
data += "</tr>";
//写出数据
foreach (DataRow row in tb.Rows)
{
count = 1;
initNum = 4;//前四列不动
baseInit = initNum;
initI = 0;
data += "<tr>";
foreach (DataColumn column in tb.Columns)
{
if (count > initNum)
{
for (int i = initI; i < list.Length; i++)
{
int ins = list[i];
if (baseInit < count && (baseInit + ins) >= count)
{
string color = i % 2 == 0 ? "#CCCC66" : "#3399CC";
data += String.Format("<td align='center' style='width:100px;' bgcolor='{1}'>{0}</td>", row[column].ToString(), color);
count++;
break;
}
else
{
baseInit += ins;
initI++;
continue;
}
}
}
else
{
data += "<td align='center' style='width:100px;'>" + row[column].ToString() + "</td>";
count++;
}
}
data += "</tr>";
}
data += "</table>";
}
return data;
}
方法二
以下方法是.framework 4.0环境下运行
using MSExcel = Microsoft.Office.Interop.Excel;//添加引用Microsoft.Office.Interop.Excel
public class ExcelHelper
{ /// <summary> /// Creates the excel file by column. /// </summary> /// <param name="filename">The filename.</param> /// <param name="columns">The columns.</param> public static void CreateExcelFileByColumn(string filename, IEnumerable<ColumnData> columns) { createExcelFile(filename, excelApp => { //Write data into the workbook by column. int columnIndex = 1; if (columns != null) { foreach (var column in columns) { //Write the header. //excelApp.Cells[1, columnIndex].Value = column.Header; ((MSExcel.Range)excelApp.Cells[1, columnIndex]).Value = column.Header; ((MSExcel.Range)excelApp.Cells[1, columnIndex]).Font.Bold = true; ((MSExcel.Range)excelApp.Cells[1, columnIndex]).Borders.ColorIndex = 0; //Write the following lines in this column. int rowIndex = 2; foreach (var cell in column.Data) { ((MSExcel.Range)excelApp.Cells[rowIndex++, columnIndex]).Value = cell; } columnIndex += 3; } } }); }/// <summary>
/// Creates the excel file by row. /// </summary> /// <param name="filename">The filename.</param> /// <param name="rows">The rows.</param> public static void CreateExcelFileByRow(string filename, IEnumerable<IEnumerable> rows) { createExcelFile(filename, excelApp => { //Write data into the workbook by row. int rowIndex = 1; if (rows != null) { foreach (var row in rows) { int columnIndex = 1; foreach (var cell in row) { ((MSExcel.Range)excelApp.Cells[rowIndex, columnIndex++]).Value = cell; } rowIndex++; } } }); }/// <summary>
/// Creates the excel file and perform the specified action. /// </summary> /// <param name="filename">The filename.</param> /// <param name="action">The action.</param> private static void createExcelFile(string filename, Action<MSExcel.Application> action) { //Create the excel application and set it to run in background. var excelApp = new MSExcel.Application(); excelApp.Visible = false;//Add a new workbook.
excelApp.Workbooks.Add();//Perform the action.
action(excelApp); //Save the workbook then close the file. excelApp.ActiveWorkbook.SaveAs(Filename: filename, FileFormat: MSExcel.XlFileFormat.xlWorkbookNormal); excelApp.ActiveWorkbook.Close();//Exit the excel application.
excelApp.Quit(); } } /// <summary> /// Represents the header and data of a column. /// </summary> [Serializable] public class ColumnData { /// <summary> /// Gets or sets the header. /// </summary> /// <value>The header.</value> public string Header { get; set; }/// <summary>
/// Gets or sets the data. /// </summary> /// <value>The data. public IEnumerable Data { get; set; } }
在页面代码里写如下
protected void ImageButton3_Click(object sender, ImageClickEventArgs e) {
string userId = Request["userId"]; Users user = DAL.ClientBusiness.UsersBase.getUsersById(userId); String fuName = DAL.ClientBusiness.UsersBase.getFullName(user); string fileName = Server.MapPath("") + "\\Excel" + String.Format("\\{0}.xls", fuName); System.IO.FileInfo fi = new System.IO.FileInfo(fileName);//excelFile为文件在服务器上的地址 if (fi.Exists == true) //存在就删除。这个的策略根据实际需求定 { fi.Delete(); } List<ColumnData> dd = generatData(); ExcelHelper.CreateExcelFileByColumn(fileName, dd); fi = new System.IO.FileInfo(fileName);HttpResponse contextResponse = HttpContext.Current.Response;
contextResponse.Redirect(String.Format("Excel/{0}", fi.Name), false); }private List<ColumnData> generatData()
{//这里是组装数据。具体应用不同。只要能组装成List<ColumnData>格式
List<ColumnData> returnValue = new List<ColumnData>(); string sectionId = Request["sectionId"]; string userId = Request["userId"]; Course course = DAL.ClientBusiness.CourseBase.getCourseBySectionId(sectionId); List<DTO.User_Section> usList = DAL.ClientBusiness.UserSectionBase.listUserSectionByUserIdAndCourseId(userId, course.ID); List<DTO.Section> sectionList = DAL.ClientBusiness.SectionBase.listSectionBySectionIds(usList.Select(ins => ins.Section_ID).Distinct().ToArray()); usList = DAL.ClientBusiness.UserSectionBase.getBySectionIds(usList.Select(ins => ins.Section_ID).Distinct().ToArray());//所有的 usList = usList.Where(ins => ins.Role_ID == "1").ToList(); List<DTO.Users> userList = DAL.ClientBusiness.UsersBase.listByUserIds(usList.Select(ins => ins.User_ID).Distinct().ToArray()); StringBuilder data = new StringBuilder();if (sectionList.Count > 0)
{ sectionList = sectionList.OrderBy(ins => ins.Section_Name).ToList();foreach (DTO.Section section in sectionList)
{ List<Users> _users = (from us in usList join user in userList on us.User_ID equals user.ID where us.Section_ID == section.ID select user).ToList(); _users = _users.OrderBy(ins => ins.Lname).ToList(); ColumnData rowData = new ColumnData(); rowData.Header = section.Section_Name; rowData.Data = _users.Select(ins => DAL.ClientBusiness.UsersBase.getFullName(ins)); returnValue.Add(rowData); } } return returnValue; }