SQL数据库,一个一万条数据,其中要经过一些处理后导出EXCEL文件,请问高手是以什么样的方案来实现!谢谢!
解决方案 »
- 均衡负载环境下 Session的设置
- FCK 2.6.3 图片上传的问题
- GridView 不能分页 怎么改啊 !!!
- socket下载
- 动态页服务器 IIS7 c# win2008 web + 静态页服务器linux+apath + linux mySQL 混合架构 做大型门户网站的可行性
- try
- asp.net支持子文件夾么
- asp.net 读取EXCEL文件出现问题,
- ▲▲▲请问各位,在asp.net中我给toolbar加了Image后,Image在页面上变的很大,其实我的图片是很小的,请问如何控制ToolBar中的图片大小呀
- 急:打不开Design可视页
- 百分超急!webservices引用怎么变成了三个文件??
- 问一个关于论坛开发的问题
private void ExportBtn_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
string strFileName="";
System.Web.UI.WebControls.CheckBox chkExport=null;
Excel.ApplicationClass excel=null;
Excel.Workbook workbook=null;
Excel.Worksheet activeSheet=null;
Excel.Worksheet sheet=null;
strFileName="平台和项目周总结和计划";
if( System.IO.File.Exists( Server.MapPath(strFileName+".xls") ) )
System.IO.File.Delete( Server.MapPath(strFileName+".xls") );
try
{
excel=new Excel.ApplicationClass();
workbook = excel.Workbooks.Add(Type.Missing);
activeSheet=(Excel.Worksheet)workbook.ActiveSheet;
int week=0;
int rowStart=1;
int rowCur=1;
string strItem=null;
int iItem=2;
foreach(DataGridItem oDataGridItem in dgWP.Items)
{
chkExport = (System.Web.UI.WebControls.CheckBox)oDataGridItem.FindControl("WPCheckBox");
if(chkExport.Checked==true)
{
string strWeek=((System.Web.UI.WebControls.Label)(oDataGridItem.FindControl("weekLabel"))).Text;
if(week!=int.Parse(strWeek))//以周为单位分页
{
sheet = (Excel.Worksheet)excel.Sheets.Add(activeSheet,Type.Missing,Type.Missing,Type.Missing);
DateTime dt=Convert.ToDateTime(oDataGridItem.Cells[4].Text);
DateTime dtStart=(int)dt.DayOfWeek==0?dt.AddDays(1):dt.AddDays(6-(int)dt.DayOfWeek);
sheet.Name=dtStart.ToShortDateString()+"计划";
FillExcelTitle(sheet);
week=int.Parse(strWeek);
rowStart=2;
rowCur=2;
}
//填写该计划的详细内容
System.Data.DataTable dtWPContent=wpContent.SelectWPContentbyWPID(oDataGridItem.Cells[1].Text).Tables[0];
if(dtWPContent.Rows.Count>0)
{
sheet.Cells[rowCur,1]=dtWPContent.Rows[0]["ProductLineName"].ToString();
for(int i=0;i<dtWPContent.Rows.Count;i++)
{
if(strItem==dtWPContent.Rows[i]["ProductTypeName"].ToString())
{
sheet.get_Range("B"+iItem.ToString(),"B"+(rowCur).ToString()).Merge(0);
sheet.get_Range("C"+iItem.ToString(),"C"+(rowCur).ToString()).Merge(0);
}
else
{
if(strItem==null) strItem=dtWPContent.Rows[i]["ProductTypeName"].ToString();
sheet.Cells[rowCur,2]=dtWPContent.Rows[i]["ProductTypeName"].ToString();
sheet.Cells[rowCur,3]=dtWPContent.Rows[i]["Frequency"].ToString();
strItem=dtWPContent.Rows[i]["ProductTypeName"].ToString();
iItem=rowCur;
}
sheet.Cells[rowCur,4]=dtWPContent.Rows[i]["ProductSpec"].ToString();
sheet.Cells[rowCur,5]=dtWPContent.Rows[i]["Content"].ToString()+" "+dtWPContent.Rows[i]["Tester"].ToString();
if(dtWPContent.Rows[i]["bTempFlag"].ToString()=="1")
{
((Excel.Range)sheet.Cells[rowCur,5]).Font.Color=0xFF0000;
((Excel.Range)sheet.Cells[rowCur,5]).Font.Bold=true;
}
sheet.Cells[rowCur,6]=exFunc.FormatDateString(dtWPContent.Rows[i]["PlanStartTime"].ToString())
+"-"+exFunc.FormatDateString(dtWPContent.Rows[i]["PlanEndTime"].ToString());
sheet.Cells[rowCur,7]=exFunc.FormatDateString(dtWPContent.Rows[i]["ExecStartTime"].ToString())
+"-"+exFunc.FormatDateString(dtWPContent.Rows[i]["ExecEndTime"].ToString());
sheet.Cells[rowCur,8]=dtWPContent.Rows[i]["FinishState"].ToString();
if(dtWPContent.Rows[i]["FinishState"].ToString()=="未完成")
{
((Excel.Range)sheet.Cells[rowCur,8]).Font.Color=0x00FF;
((Excel.Range)sheet.Cells[rowCur,8]).Font.Bold=true;
}
sheet.Cells[rowCur,9]=dtWPContent.Rows[i]["Conclusion"].ToString();
rowCur++;
}
sheet.get_Range("A"+rowStart.ToString(),"A"+(rowCur-1).ToString()).Merge(0);
sheet.get_Range("A"+rowStart.ToString(),"I"+(rowCur-1).ToString()).Interior.Color=RandomColor();
iItem=rowCur;
rowStart=rowCur;
}
}
}
workbook.SaveAs(Server.MapPath(strFileName),Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,
null,null);
Response.AppendHeader("Content-Disposition","attachment;filename="+Server.UrlEncode(strFileName)+".xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("gb2312");
Page.Response.ContentType = "Application/ms-excel";
Response.WriteFile(strFileName+".xls");
Response.Flush();
Response.Clear();
Response.End();
workbook.Close(null,null,null);
workbook=null;
GC.Collect();
GC.WaitForPendingFinalizers();
excel.Workbooks.Close();
excel.Quit();
excel=null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch(Exception ex)
{
workbook.Close(null,null,null);
workbook=null;
GC.Collect();
GC.WaitForPendingFinalizers();
excel.Workbooks.Close();
excel.Quit();
excel=null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void FillExcelTitle(Excel.Worksheet sheet)
{
sheet.Columns.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft;
sheet.Columns.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;
sheet.Columns.Font.Size=10;
sheet.Columns.Borders.Color=0x000000;
sheet.Columns.Borders.LineStyle=1;
sheet.get_Range("A1","I1").Font.Bold=true;
sheet.get_Range("A1","I1").Interior.Color=0xE0FFFF;
sheet.get_Range("A1","I1").HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft;
sheet.get_Range("A1","I1").VerticalAlignment =Excel.XlVAlign.xlVAlignCenter;
sheet.get_Range("A1","A1").ColumnWidth=8;
sheet.get_Range("A1","A1").Value2="产品线";
sheet.get_Range("B1","B1").ColumnWidth=10;
sheet.get_Range("B1","B1").Value2="产品型号";
sheet.get_Range("C1","C1").ColumnWidth=8;
sheet.get_Range("C1","C1").Value2="频段";
sheet.get_Range("D1","D1").ColumnWidth=12;
sheet.get_Range("D1","D1").Value2="产品规格";
sheet.get_Range("E1","E1").ColumnWidth=35;
sheet.get_Range("E1","E1").Value2="工作内容和责任人";
sheet.get_Range("F1","F1").ColumnWidth=20;
sheet.get_Range("F1","F1").Value2="计划起止时间";
sheet.get_Range("G1","G1").ColumnWidth=20;
sheet.get_Range("G1","G1").Value2="执行起止时间";
sheet.get_Range("H1","H1").ColumnWidth=8;
sheet.get_Range("H1","H1").Value2="完成情况";
sheet.get_Range("I1","I1").ColumnWidth=10;
sheet.get_Range("I1","I1").Value2="总结";
sheet.get_Range("E1","E1").EntireColumn.WrapText=true;
sheet.get_Range("I1","I1").EntireColumn.WrapText=true;
}
先开个fso写csv,之后一次性贴到excel合适的位置就ok拉