大家好,有来论坛蹭知识了,希望大哥们 多多关照,带带一个当入门的苦逼程序员---小弟今天 真郁闷,写了个ASP.net 将数据导出到EXCEL 本来写好了的,能实现功能了,突然老大说,另加一个表(table),如果2个table导出2个EXCEL 还好解决,可偏偏,非要导出在一个EXCEL,就是说点了下按钮,就导出一个EXCEL里的2个sheet,这2个sheet 分别放的就是2个table,求 大哥哥大姐姐们,给予帮助。我一个的导出是能实现的
代码如下:
public class PKBAnalysis : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// 得到下载文件的存放跟目录,并格式化一下。如:E:\Temp\eSupport
string folderPath = this.GetUploadFolderPath();
// 得到页面上的所有的查询结果
string executeSql = "exec usp_pkb_analysis";//这就是一个查询出来的值
DataTable dataSource = new SQLManual().GetDataSet(executeSql).Tables[0];
/*红色区域就是一个table,现在 还有一个DataTable2,本来想像这样导出的,可实现不了加sheet的操作
求大牛门,帮帮忙,怎么把DataTable1和DataTable2,导出到一个表里,分2个sheet呢*/ // 保存Excel,得到Excel文件的存放路径
string excelPath = this.SaveExcel(folderPath, dataSource);
// 下载Excel文件
new FileBusiness().DownloadFile(excelPath);
} private string GetUploadFolderPath()
{
string folderPath = string.Empty;
FolderMappingEntity folderMappingEntity = new FolderMappingEntity();
folderMappingEntity.QueryMode = true;
folderMappingEntity.Diskfull += "N";
folderMappingEntity.AddOrderField(folderMappingEntity.Priority);
DataSet dataSetFolder = folderMappingEntity.Query();
if(dataSetFolder.Tables[0].Rows.Count != 0)
{
// 得到上传文件的存放跟目录,并格式化一下。如:E:\Temp\eSupport
folderPath = dataSetFolder.Tables[0].Rows[0][folderMappingEntity.Physicaldir.PhysicalFieldName].ToString().Trim().TrimEnd('\\').TrimEnd('/') + @"\";;
folderPath = folderPath + @"PKB\Download\";
}
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
return folderPath;;
} private string SaveExcel(string saveFolderPath, DataTable dataSource)
{
string savePath = saveFolderPath + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
QExcel excel = new QExcel();
excel.QWorkBook.HeadStyle = QStyleLib.DefaultHeadStlye;
excel.QWorkBook.SheetRowLimit = 5000; // work sheet最大绑定行数 foreach (DataRow dataRow in dataSource.Rows)
{
dataRow["UploadDate"] = new StringHelper().FormatDate(dataRow["UploadDate"], false);
}
dataSource.AcceptChanges(); QWorkSheet workSheet = new QWorkSheet();
workSheet.DataSource = dataSource;
// 指定栏位名称
workSheet.ColumnNames = new string[] {"a", "b.", "c", "d", "e", "f", "g"};
// 指定列对应的数据栏位
workSheet.ColumnFields = new string[] {"aa", "bb", "cc", "dd", "ee", "ff", "gg"};
// 设定每列宽度,数组长度要与ColumnFields对应
workSheet.ColumnWidths = new int[] {100, 100, 100, 100, 90, 100, 80};
// 指定每栏的数据格式,0: string, 1: number, 2:DateTime
workSheet.ColumnDataTypes = new int[] {0, 0, 0, 0, 1, 1, 1};
// 指定每一列的样式,数组长度要与ColumnFields对应
workSheet.ColumnStyles = new QBaseStyle[] {null, null, null, null, null, null, null};
excel.QWorkBook.Add(workSheet);
excel.SaveAs(savePath);
return savePath;
代码如上,单个DataTable导出是ok的 现在要再加一个table 怎么实现 一个EXCEL分2个sheet导出啊
小弟谢谢了,分多点。100吧
代码如下:
public class PKBAnalysis : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
// 得到下载文件的存放跟目录,并格式化一下。如:E:\Temp\eSupport
string folderPath = this.GetUploadFolderPath();
// 得到页面上的所有的查询结果
string executeSql = "exec usp_pkb_analysis";//这就是一个查询出来的值
DataTable dataSource = new SQLManual().GetDataSet(executeSql).Tables[0];
/*红色区域就是一个table,现在 还有一个DataTable2,本来想像这样导出的,可实现不了加sheet的操作
求大牛门,帮帮忙,怎么把DataTable1和DataTable2,导出到一个表里,分2个sheet呢*/ // 保存Excel,得到Excel文件的存放路径
string excelPath = this.SaveExcel(folderPath, dataSource);
// 下载Excel文件
new FileBusiness().DownloadFile(excelPath);
} private string GetUploadFolderPath()
{
string folderPath = string.Empty;
FolderMappingEntity folderMappingEntity = new FolderMappingEntity();
folderMappingEntity.QueryMode = true;
folderMappingEntity.Diskfull += "N";
folderMappingEntity.AddOrderField(folderMappingEntity.Priority);
DataSet dataSetFolder = folderMappingEntity.Query();
if(dataSetFolder.Tables[0].Rows.Count != 0)
{
// 得到上传文件的存放跟目录,并格式化一下。如:E:\Temp\eSupport
folderPath = dataSetFolder.Tables[0].Rows[0][folderMappingEntity.Physicaldir.PhysicalFieldName].ToString().Trim().TrimEnd('\\').TrimEnd('/') + @"\";;
folderPath = folderPath + @"PKB\Download\";
}
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
return folderPath;;
} private string SaveExcel(string saveFolderPath, DataTable dataSource)
{
string savePath = saveFolderPath + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
QExcel excel = new QExcel();
excel.QWorkBook.HeadStyle = QStyleLib.DefaultHeadStlye;
excel.QWorkBook.SheetRowLimit = 5000; // work sheet最大绑定行数 foreach (DataRow dataRow in dataSource.Rows)
{
dataRow["UploadDate"] = new StringHelper().FormatDate(dataRow["UploadDate"], false);
}
dataSource.AcceptChanges(); QWorkSheet workSheet = new QWorkSheet();
workSheet.DataSource = dataSource;
// 指定栏位名称
workSheet.ColumnNames = new string[] {"a", "b.", "c", "d", "e", "f", "g"};
// 指定列对应的数据栏位
workSheet.ColumnFields = new string[] {"aa", "bb", "cc", "dd", "ee", "ff", "gg"};
// 设定每列宽度,数组长度要与ColumnFields对应
workSheet.ColumnWidths = new int[] {100, 100, 100, 100, 90, 100, 80};
// 指定每栏的数据格式,0: string, 1: number, 2:DateTime
workSheet.ColumnDataTypes = new int[] {0, 0, 0, 0, 1, 1, 1};
// 指定每一列的样式,数组长度要与ColumnFields对应
workSheet.ColumnStyles = new QBaseStyle[] {null, null, null, null, null, null, null};
excel.QWorkBook.Add(workSheet);
excel.SaveAs(savePath);
return savePath;
代码如上,单个DataTable导出是ok的 现在要再加一个table 怎么实现 一个EXCEL分2个sheet导出啊
小弟谢谢了,分多点。100吧
http://dotnet.aspx.cc/file/Export-Gridview-To-Excel-With-Multi-Sheet.aspx
在帮帮我那
看方法了吗?跟数据没关系,2条数据也能放到2个sheet里面
参考一下下面的http://blog.csdn.net/cdsnaspnet/article/details/5612150