当前位置：文江博客话题详情

ASP.NET - 能够将 CSV/excel 导出到多个工作表吗？

发布于 2024-12-18 17:09:08 字数 146 浏览 5 评论 0原文

我想知道是否有人有一个解决方案能够将数据导出到多个工作表中的excel中...到目前为止我只知道如何将多个对象导出到一个工作表中！

我正在使用 Devexpress 导出器？似乎他们不支持这一点，我也可以采用通用解决方案吗？

任何指导将不胜感激！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往日情怀 2024-12-25 17:09:08

埃普拉斯？

http://epplus.codeplex.com/releases/view/42439

我在winforms 应用程序，它很棒。但它只处理 xlsx 文件 - 即 Excel 2007 及以上版本。

安德鲁

回复收藏 0 原文

浴红衣 2024-12-25 17:09:08

文件路径可能应该是 server.mappath()，因为生成的 excel 应首先保存在服务器路径中并且可以允许下载。
添加引用 Excel 并添加命名空间“Using Excel”。

   public void Export(DataSet ds, string filePath)
    {
        string data = null;
        string columnName = null;
        int i = 0;
        int j = 0;
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;
        Excel.Worksheet xlWorkSheet = null;
        object misValue = System.Reflection.Missing.Value;
        Excel.Range range;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


        for (int l = 0; l < ds.Tables.Count; l++)
        {
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(l + 1);                
            xlWorkSheet.get_Range("A1:D1", Type.Missing).Merge(Type.Missing);
            xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet.Cells.Font.Name = "Courier New";

            for (i = 0; i <= ds.Tables[l].Rows.Count - 1; i++)
            {


                for (j = 0; j <= ds.Tables[l].Columns.Count - 1; j++)
                {
                    columnName = ds.Tables[l].Columns[j].ColumnName.ToString();
                    xlWorkSheet.Cells[3, j + 1] = columnName;
                    data = ds.Tables[l].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 5, j + 1] = data;
                }
            }
        }

        xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        // kill all excel processes
        Process[] pros = Process.GetProcesses();
        for (int p = 0; p < pros.Length; p++)
        {
            if (pros[p].ProcessName.ToLower().Contains("excel"))
            {
                pros[p].Kill();
                break;
            }
        }

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

filepath should probably be server.mappath() as generated excel shoul first be saved in server path and can be allowed to download.
Add reference Excel and add namespace "Using Excel".

   public void Export(DataSet ds, string filePath)
    {
        string data = null;
        string columnName = null;
        int i = 0;
        int j = 0;
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;
        Excel.Worksheet xlWorkSheet = null;
        object misValue = System.Reflection.Missing.Value;
        Excel.Range range;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


        for (int l = 0; l < ds.Tables.Count; l++)
        {
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(l + 1);                
            xlWorkSheet.get_Range("A1:D1", Type.Missing).Merge(Type.Missing);
            xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
            xlWorkSheet.Cells.Font.Name = "Courier New";

            for (i = 0; i <= ds.Tables[l].Rows.Count - 1; i++)
            {


                for (j = 0; j <= ds.Tables[l].Columns.Count - 1; j++)
                {
                    columnName = ds.Tables[l].Columns[j].ColumnName.ToString();
                    xlWorkSheet.Cells[3, j + 1] = columnName;
                    data = ds.Tables[l].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 5, j + 1] = data;
                }
            }
        }

        xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        // kill all excel processes
        Process[] pros = Process.GetProcesses();
        for (int p = 0; p < pros.Length; p++)
        {
            if (pros[p].ProcessName.ToLower().Contains("excel"))
            {
                pros[p].Kill();
                break;
            }
        }

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

回复收藏 0 原文

紫竹語嫣☆ 2024-12-25 17:09:08

去年年初我遇到了完全相同的问题。最终，我能够自动生成一个 .xlt Excel 文件，其中包含多个 Excel 可以接受的电子表格（实际上是网页）。

我知道这不漂亮，但它对我有用。如果您决定尝试一下，请告诉我它是否也适合您。

输出以下内容（您可以调整为具有所需的任意数量的工作表）：

MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_01CB53EC.0FFDF540"

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=utf-8"

<html xmlns:v=3D"urn:schemas-microsoft-com:vml"
xmlns:o=3D"urn:schemas-microsoft-com:office:office"
xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>

<link id=3D"shLink" href=3D"Book1_files/Sheet1.htm">
<link id=3D"shLink" href=3D"Book1_files/Sheet2.htm">
<link id=3D"shLink" href=3D"Book1_files/Sheet3.htm">

<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet1.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet2.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet3.htm"/>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml>
</head>

</html>

对于每个页面，输出：

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1_files/{ your sheet name }".htm
Content-Type: text/html; charset=utf-8

<html>
{ your stuffs }
</html>

End for循环

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1_files/filelist.xml
Content-Transfer-Encoding: quoted-printable
Content-Type: text/xml; charset="utf-8"

<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
 <o:MainFile HRef=3D"../Book1.htm"/>
 <o:File HRef=3D"Sheet1.htm"/>
 <o:File HRef=3D"Sheet2.htm"/>
 <o:File HRef=3D"Sheet3.htm"/>
</xml>
------=_NextPart_01CB53EC.0FFDF540--

在代码中，将响应缓冲区设置为 false，内容类型设置为“application/vnd.ms-excel”并添加标题“Content-Disposition”，“inline; filename={ 您的文件名}”。请注意，输出文件的第一行之前不得有换行符。

I faced the exact same problem early last year. Eventually I was able to hack my way into automate generation of a .xlt Excel file with multiple spreadsheets (which are actually webpages) that can be accepted by Excel.

I know it's not pretty, but it worked for me. If you decide to try it, let me know if it works for you too.

Output the followings (you can adjust to have any number of sheets you want):

MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_01CB53EC.0FFDF540"

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=utf-8"

<html xmlns:v=3D"urn:schemas-microsoft-com:vml"
xmlns:o=3D"urn:schemas-microsoft-com:office:office"
xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>

<link id=3D"shLink" href=3D"Book1_files/Sheet1.htm">
<link id=3D"shLink" href=3D"Book1_files/Sheet2.htm">
<link id=3D"shLink" href=3D"Book1_files/Sheet3.htm">

<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet1.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet2.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetSource HRef=3D"Book1_files/Sheet3.htm"/>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml>
</head>

</html>

For each page, output:

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1_files/{ your sheet name }".htm
Content-Type: text/html; charset=utf-8

<html>
{ your stuffs }
</html>

End for loop

------=_NextPart_01CB53EC.0FFDF540
Content-Location: file:///C:/CE594991/Book1_files/filelist.xml
Content-Transfer-Encoding: quoted-printable
Content-Type: text/xml; charset="utf-8"

<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
 <o:MainFile HRef=3D"../Book1.htm"/>
 <o:File HRef=3D"Sheet1.htm"/>
 <o:File HRef=3D"Sheet2.htm"/>
 <o:File HRef=3D"Sheet3.htm"/>
</xml>
------=_NextPart_01CB53EC.0FFDF540--

In your code, set response buffer to false, content type to "application/vnd.ms-excel" and add the header "Content-Disposition", "inline; filename={ your file name }". Note there must be no line breaks before the first line of your output file.

回复收藏 0 原文