目前常用的两种方法：
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='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='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='100px;' bgcolor='{1}'>{0}</td>", row[column].ToString(), color);
count++;
break;
}
else
{
baseInit += ins;
initI++;
continue;
}
}
}
else
{
data += "<td align='center' style='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;
}