高手进来,如何在Asp.net2.0中生成excel文档!
如何在Asp.net2.0中生成excel文档,需要怎样的.net环境?
比如通过点击按钮生成excel表,完成编辑后保存到服务器.最好附上代码.
[解决办法]
我在网上找的例子，又根据自己的需求改了改，供参考。
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;
}
------解决方案--------------------
这里面有很不错的例子一定会对你有所帮助
http://blog.csdn.net/zuoyefeng_com/archive/2007/06/01/1633643.aspx
[解决办法]
前台： <asp:FileUpload ID= "fileUP " runat= "server " />
<asp:Button ID= "Button1 " runat= "server " OnClick= "Button1_Click " Text= "导入数据 " />
if (FileUpload1.HasFile)
{
//得到文件名
string filename = FileUpload1.FileName;
//取得扩展名
int i = filename.LastIndexOf( ". ");
string newext = filename.Substring(i);
//根据时间文件大小重命名
DateTime now = DateTime.Now;
string newname = now.DayOfYear.ToString() +now.Minute.ToString()+FileUpload1.PostedFile.ContentLength.ToString();
//保存的路径
string savepath = Server.MapPath( "~\\UploadFileTemp\\ " + newname + newext);
FileUpload1.PostedFile.SaveAs(Server.MapPath( "~\\UploadFileTemp\\ " + newname + newext));
}
