我想在服务器上先建一个excel模板,然后用户在客户端点击生成数据按钮后,服务器往excel模板里写数据(具体数据是从数据库中查询出来的),数据写完后提示客户端下载该文件最好在服务器端不要安装office,客户端已经安装office
解决方案 »
- 请教ASP.NET中的OleDbTransaction
- ◆◆◆ “过客”进来帮忙解决一下,正则问题 ◆◆◆
- 大家都用什么软件做页面?我原来用dw8
- 一个树的查询问题,急,急 在线等
- 在asp.netk 使用File的时候出错,各位兄弟帮忙呀
- 高手 大侠 无论你是什么级别,都进来看看啊,帮帮我啊
- 登录有验证码的问题~
- 关于Cache的缓存依赖。
- Login Control, Conver numeric to decimal ,Page is loading. Please wait
- 您要访问的网页有问题,无法显示! 这是什么错误?恶心,又不说哪里错。
- 不知后台要调用前台JavaScript的方法,怎么调用呀?
- 关于模式对话框的问题
Response.AppendHeader("content-disposition", "filename=output.xls");
Response.ContentType = "application/vnd.ms-excel"; //将表格内容作为Excel输出
System.IO.StringWriter sw=new System.IO.StringWriter();
HtmlTextWriter htwVehicleList=new HtmlTextWriter(sw);
this.Page.RenderControl(htwVehicleList);
Response.Write(sw.ToString());
Response.End();
http://www.cnblogs.com/lovecherry/archive/2005/03/25/125519.html
可能不一定能达到你的要求
你可以参考
http://www.cnblogs.com/huobazi/archive/2004/04/27/7771.aspx
2. 将模版文件复制成新的用户Download文件,可以采用SessionId + 系统时间,以保证生成文件的唯一。
3. 对生成文件进行操作,将数据从DataSet等导入Excel.
4. 打开新的页面,将临时文件"写"到客户端.
第一步,略
第二,三步:
Public Shared Function MakeExcel(ByVal sessionId As String, ByRef sltReport As String, ByVal sltData As String) As String
'Public Shared Function MakeExcel(ByVal sessionId As String, ByVal sltReport As String, ByVal sltData As String, ByVal rootPath As String) As String
' New the web service.
Dim BSvc As BusinessWebService.BusinessWebSvc = New BusinessWebService.BusinessWebSvc
'1. From db to string
'2. Set string to clipboard
'3. New a excel file by copy from template file
'4. Open the excel file, paste data to excel
'5. Format the excel file then close it Dim destFilePath As String = System.Configuration.ConfigurationSettings.AppSettings("workDir")
Dim templateFilePath As String = System.Configuration.ConfigurationSettings.AppSettings("templateDir")
'Dim destFilePath As String = rootPath & System.Configuration.ConfigurationSettings.AppSettings("workDir")
'Dim templateFilePath As String = rootPath & System.Configuration.ConfigurationSettings.AppSettings("templateDir")
Dim stringData As String
Dim templateFileName As String Dim pasteCells As String()
Try
Select Case sltReport
Case "2" '損益予算申請総括表(損予№1)_PF0010
sltReport = "損益予算申請総括表(損予№1)_PF0010.xls"
templateFileName = System.Configuration.ConfigurationSettings.AppSettings("PF0010")
Dim cells As String() = {""}
pasteCells = cells
stringData = DoPF0010(BSvc)
Case "3" '損益予算実施状況_PF0020
sltReport = "PF0020.xls"
templateFileName = System.Configuration.ConfigurationSettings.AppSettings("PF0020")
Dim cells As String() = {"E5", "E7", "E22"}
pasteCells = cells
stringData = DoPF0020(BSvc)
Case "4" '委託費期末予想調書_PF0030
sltReport = "PF0030.xls"
templateFileName = System.Configuration.ConfigurationSettings.AppSettings("PF0030")
Dim cells As String() = {"E5", "E7", "E22"}
pasteCells = cells
stringData = DoPF0030(BSvc)
End Select Select Case sltData
Case "2"
Case "3"
Case "4"
End Select templateFilePath = templateFilePath & "\" & templateFileName
destFilePath = destFilePath & "\" & sessionId & DateTime.Now.ToString("hhmmss") & ".xls" CopyFromTemplate(destFilePath, templateFilePath) ClipboardToExcel(destFilePath, stringData, pasteCells) Catch ex As Exception
Throw ex
End Try
Return destFilePath
End Function第四步:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim FilePath As String = Session("FilePath").ToString()
If FilePath Is Nothing Then
Response.Write("<h1>Server is unusable!</h1>")
Else
Try
Dim saveAsFileName As String = Session("SaveAsFileName").ToString()
'Dim fs As System.IO.FileStream = System.IO.File.OpenRead(FilePath)
'Dim FileData(fs.Length) As Byte
'fs.Read(FileData, 0, CType(fs.Length, Integer))
'fs.Close()
'Session.Clear()
Response.AddHeader("Content-Type", "application/vnd.ms-excel") Dim str As String = saveAsFileName
str = HttpUtility.UrlEncode(saveAsFileName, System.Text.Encoding.UTF8) Response.AddHeader("Content-Disposition", "inline;filename=" & str)
'Response.AddHeader("Content-Length", FileData.Length.ToString())
'Response.BinaryWrite(FileData)
Response.WriteFile(FilePath)
Catch ex As Exception
Response.Write("<h1>Server is unusable!</h1>")
End Try
Response.End()
' 'Dim xlFileName As String = FilePath.Substring(FilePath.LastIndexOf("\"))
' 'Response.Redirect("../Excel/work" & xlFileName) End If
End If
End Sub
我的要求其实很简单:就是我有excel 模板了,就是把数据库里的数据写到excel模板中不知道能不能通过owc解决,我通过owc能够生成excel,但是就是不能打开excel 模板,再对模板进行操作还望高手指点,救急啊
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
//设置输出流为简体中文
Response.ContentType = "application/ms-excel";
//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
private void MakeExcel(string strData, string destFilePath)
{
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
Excel.Range xlCell; if( xlApp == null )
xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
object objNull = System.Reflection.Missing.Value;
xlWorkbook = xlApp.Workbooks.Open( destFilePath, objNull, objNull,
objNull, objNull, objNull,
objNull, objNull, objNull,
objNull, objNull, objNull,
objNull );
this.Application.Lock();
System.Windows.Forms.Clipboard.SetDataObject(strData);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets[1];
xlCell = (Excel.Range)xlWorksheet.Cells[2,1];
xlCell.Select();
xlWorksheet.Paste(objNull,objNull);
System.Windows.Forms.Clipboard.SetDataObject("");
this.Application.UnLock();
int intEndColumn = xlWorksheet.UsedRange.Columns.Count;
int intEndRow = xlWorksheet.UsedRange.Rows.Count;
Excel.Range xlRange=xlWorksheet.Cells.get_Range(xlWorksheet.Cells[2,1],xlWorksheet.Cells[intEndRow,intEndColumn]); SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft) );
SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight) );
SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop) );
SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom) );
SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal) );
SetExcelBorderStyle( xlRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical) ); xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges , destFilePath, objNull);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
}