使用microsoft.interope.excel在客户端保存excel [英] saving excel on client side using microsoft.interope.excel

问题描述

大家好，



我创建了应用程序，我的sql数据转换为excel文件，我这样做是通过使用microsoft.interop.excel，但我在客户端下载此excel时遇到问题。我试图微软的工作簿.SaveAs（）函数，但这个函数在服务器端保存文件，而不是在客户端，任何人都可以帮助我在客户端保存这个excel。



以下是我的代码

 Microsoft.Office.Interop.Excel.Application excel =  new  Microsoft.Office.Interop.Excel.Application（）; 
  //   excel.DefaultFilePath = Server.MapPath（@〜\ SaveEcel \）;  
 
 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add（ true ）; 
 Microsoft.Office.Interop.Excel.Worksheet ws =（Microsoft.Office.Interop.Excel.Worksheet）workbook.Worksheets [ 1 ]; 
 
 System.Data.DataTable dt = ds.Tables [ 0 ]; 
  int  iCol =  0 ; 
  foreach （DataColumn c  in  dt.Columns）
 {
 ICOL ++; 
 ws.Cells [ 1 ，iCol] = c.ColumnName; 
 Microsoft.Office.Interop.Excel.Range _range =（Microsoft.Office.Interop.Excel.Range）ws.Cells [ 1 ，iCol]; 
  if （iCol <  =  72 ）
 {
 _range.Font.Bold =  true ; 
 _range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.FromArgb（ 230 ， 185 ， 184 ））; 
 _range.Cells.RowHeight =  77 ; 
 _range.Borders.Color = System.Drawing.Color.Black.ToArgb（）; 
 _range.Horizo​​ntalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; 
 _range.WrapText =  true ; 
 _range.Columns.AutoFit（）; 
 _range.Rows.AutoFit（）; 
} 
  else  
 {
 _range.Font.Bold =  true ; 
 _range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.FromArgb（ 216 ， 216 ， 216 ））; 
 _range.Cells.RowHeight =  77 ; 
 _range.Borders.Color = System.Drawing.Color.Black.ToArgb（）; 
 _range.Horizo​​ntalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; 
 _range.WrapText =  true ; 
 _range.Columns.AutoFit（）; 
 _range.Rows.AutoFit（）; 
} 
} 
  //  每行数据...  
  int  iRow =  0 ; 
  foreach （DataRow r  in  dt.Rows）
 {
 iRow ++; 
 iCol =  0 ; 
  foreach （DataColumn c  in  dt.Columns）
 {
 ICOL ++; 
 ws.Cells [iRow +  1 ，iCol] = r [c.ColumnName]; 
 Microsoft.Office.Interop.Excel.Range _range =（Microsoft.Office.Interop.Excel.Range）ws.Cells [iRow +  1 ，iCol] ; 
 _range.Borders.Color = System.Drawing.Color.Black.ToArgb（）; 
 _range.Rows.AutoFit（）; 
  if （c.ColumnName ==  高级编号）
 {
 ws.Cells [iRow +  1 ， 1 ] = iRow; 
} 
  if （c.ColumnName ==   Policy_Num）
 {
 _range.NumberFormat =   ####################; 
} 
  if （c.ColumnName ==  新开始日期）
 {
 _range.NumberFormat =   DD-MMM-YY; 
} 
  if （c.ColumnName ==  新开始月份）
 {
 _range.NumberFormat =   DD-MMM-YY; 
} 
  if （c.ColumnName ==   MGG年和月）
 {
 _range.NumberFormat =   DD-MMM-YY; 
} 
  if （c.ColumnName ==  声明文件日期）
 {
 _range.NumberFormat =   DD-MMM-YY; 
} 
  if （c.ColumnName ==   Vehicle Age_N）
 {
 _range.NumberFormat =   0.00\" ; 
} 
  if （c.ColumnName ==   LTGIC Rate_N）
 {
 _range.NumberFormat =   0.000\" ; 
} 
  if （c.ColumnName ==   Discount Rate_N）
 {
 _range.NumberFormat =   0.000\" ; 
} 
  if （c.ColumnName ==   Basic_OD_N）
 {
 _range.NumberFormat =   0\" ; 
} 
} 
} 
 
  object  missing = System.Reflection.Missing.Value; 
  //   string filePath = Server.MapPath（@〜\ SaveEcel \）;  
 
  string  filename = ExcelName +   XLSX。; 
  //   if（File.Exists（filename）） 
  //   File.Delete（filename）;  

workbook.SaveAs（（对象）（文件名），丢失，丢失，丢失，丢失，丢失，

Microsoft.Office.Interop。 Excel.XlSaveAsAccessMode.xlNoChange，

缺失，丢失，缺失，缺失，缺失）;

（ （Microsoft.Office.Interop.Excel._Application）EXCEL）.Quit（）; 
 excel.Visible =  false ; 

提前致谢。

解决方案

问题的根源是缺乏对HTTP工作方式的理解。您可以在服务器端保存文件，只生成通常的HTML锚元素（< a href =...>生成的Excel文件< a /> ），这样用户就可以在客户端下载/打开它。



或者你可以在HTTP响应中生成文件的内容。类 System.Web.HttpResponse 上的这个MSDN帮助页面提供了一个代码示例，它应该立即给出你如何做的想法：http://msdn.microsoft.com/en-us/library/system.web.httpresponse.aspx [ ^ ]。



当然，您应该生成Excel，而不是代码示例中显示的JPEG文件。据我了解，您已经知道如何在服务器端生成它。您需要立即将Excel文档保存到流 Response.OutputStream 。



由于浏览器不支持此内容类型，在客户端，用户将收到保存文件或打开文件的请求，在第二种情况下，默认应用程序（如果有）将尝试加载内容。

-SA

Hii all，



现在我我正在做同样的想法，无论你说什么，

i将我的数据转换为excel将其保存到服务器上然后我通过一些代码从服务器下载excel。



但我的问题是，我不希望将excel保存在服务器上

，因为它有几个原因

1.如果我将它保存在服务器上，同时如果有人试图将文件放在服务器上它会给出错误

2.或者同时如果有人想要读取该文件它会给出问题正在使用文件。

3.或ev如果我在客户端生成和下载后删除该文件，则不允许我这样做，因为文件正在使用中。



这些是我遇到的问题。

i用于我的一个项目的动态web twain，完全不同的概念，但最后他们有javascript函数，允许用户在客户端保存文件。

你能想到的任何东西，以及哪些可以让我在客户端保存这些东西都会很棒......我不想把它保存在服务器上......因为上面提到的问题。

Hello all,

I have created application where my sql data is getting converted to excel file, and i am doing this by using microsoft.interop.excel, but i am having issues while downloading this excel on client side. I trid Microsoft''s workbook.SaveAs() function but this function is saving file on server side, not on client side, can anybody help me for saving this excel on client side.

below is my code

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//  excel.DefaultFilePath = Server.MapPath(@"~\SaveExcel\");

Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

System.Data.DataTable dt = ds.Tables[0];
int iCol = 0;
foreach (DataColumn c in dt.Columns)
{
    iCol++;
    ws.Cells[1, iCol] = c.ColumnName;
    Microsoft.Office.Interop.Excel.Range _range = (Microsoft.Office.Interop.Excel.Range)ws.Cells[1, iCol];
    if (iCol <= 72)
    {
        _range.Font.Bold = true;
        _range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(230, 185, 184));
        _range.Cells.RowHeight = 77;
        _range.Borders.Color = System.Drawing.Color.Black.ToArgb();
        _range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        _range.WrapText = true;
        _range.Columns.AutoFit();
        _range.Rows.AutoFit();
    }
    else
    {
        _range.Font.Bold = true;
        _range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(216, 216, 216));
        _range.Cells.RowHeight = 77;
        _range.Borders.Color = System.Drawing.Color.Black.ToArgb();
        _range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        _range.WrapText = true;
        _range.Columns.AutoFit();
        _range.Rows.AutoFit();
    }
}
// for each row of data...
int iRow = 0;
foreach (DataRow r in dt.Rows)
{
    iRow++;
    iCol = 0;
    foreach (DataColumn c in dt.Columns)
    {
        iCol++;
        ws.Cells[iRow + 1, iCol] = r[c.ColumnName];
        Microsoft.Office.Interop.Excel.Range _range = (Microsoft.Office.Interop.Excel.Range)ws.Cells[iRow + 1, iCol];
        _range.Borders.Color = System.Drawing.Color.Black.ToArgb();
        _range.Rows.AutoFit();
        if (c.ColumnName == "Sr No.")
        {
            ws.Cells[iRow + 1, 1] = iRow;
        }
        if (c.ColumnName == "Policy_Num")
        {
            _range.NumberFormat = "####################";
        }
        if (c.ColumnName == "New Start Date")
        {
            _range.NumberFormat = "dd-MMM-yy";
        }
        if (c.ColumnName == "New Start Month")
        {
            _range.NumberFormat = "dd-MMM-yy";
        }
        if (c.ColumnName == "MGG Year and month")
        {
            _range.NumberFormat = "dd-MMM-yy";
        }
        if (c.ColumnName == "Claim File Date")
        {
            _range.NumberFormat = "dd-MMM-yy";
        }
        if (c.ColumnName == "Vehicle Age_N")
        {
            _range.NumberFormat = "0.00";
        }
        if (c.ColumnName == "LTGIC Rate_N")
        {
            _range.NumberFormat = "0.000";
        }
        if (c.ColumnName == "Discount Rate_N")
        {
            _range.NumberFormat = "0.000";
        }
        if (c.ColumnName == "Basic_OD_N")
        {
            _range.NumberFormat = "0";
        }
    }
}

object missing = System.Reflection.Missing.Value;
// string filePath = Server.MapPath(@"~\SaveExcel\");

string filename = ExcelName + ".xlsx";
//if (File.Exists(filename))
//    File.Delete(filename);

workbook.SaveAs((object)(filename), missing, missing, missing, missing, missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);

((Microsoft.Office.Interop.Excel._Application)excel).Quit();
excel.Visible = false;

Thanks in advance.

解决方案

The root of the problem is some lack of understanding of how HTTP works. You can save the file on the server side and just generate the usual HTML anchor element (<a href="...">generated Excel file<a/>), so the user will be able to download/open it on the client side.

Or you can just generate the content of the file in HTTP response. This MSDN help page on the class System.Web.HttpResponse provides a code sample which should immediately give your the idea on how to do it: http://msdn.microsoft.com/en-us/library/system.web.httpresponse.aspx[^].

Of course, you should generate Excel, not JPEG file as shown in the code sample. As I understand, you already know how to generate it on the server side. You need to save the Excel document immediately to the stream Response.OutputStream.

As the browsers do not support this content type, on the client side, the user will get a request to save file or to open it, in the second case, the default application, if any, will try to load the content.

—SA

Hii all,

For now i am doing same think whatever u said,
i am converting my data to excel saving it onto the server and then i am downloading that excel from server by some code.

but my problem is, i don''t want that excel to be save on the server
as it has several reasons
1. If i save it on the server, at the same time if somebody else tries to put file on the server it was giving error
2. Or at the same time if somebody wants to read that file it was giving problem as file is in use.
3. or even if i delete that file after genrating and downloading on client side it was not allowing me to do so because file is in use.

These are the problems which i faced.
i used dynamic web twain for one of my projects, Completely differenct concept but at the end they had javascript function which was allowing user to save file on client side.
Anything which u can think, and which can allow me to save this on client side will be greatfull..i dont want to save it on the server... because of problems mentioned above.

这篇关于使用microsoft.interope.excel在客户端保存excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！