导出到Excel的问题 [英] Problem with export to excel

问题描述

我正在使用Web应用程序。在那里，我应该将数据导出到excel。为此，我已经使用了EPPlus。



我搜索了很多但是无法找到在Excel工作表顶部添加额外行的方法。就像，我想要为了显示公司名称而在标题行的顶部排（它看起来像3或4行合并）



我试过合并标题，但后来它将只显示单个名称（不是所有标题）



代码如下。

 ws.Cells [  A1]。LoadFromDataTable（dt， true ）; 
 ws.Cells [ 1 ， 1 ]。值=   report  //  标题名称 
 ws.Cells [ 1 ， 1 ， 1 ，dt.Columns.Count] .Merge =  true ;  //  合并列的开始和结束范围 
 ws.Cells [ 1 ， 1 ， 1 ，dt.Columns.Count] .Style .Font.Bold =  true ;  //  字体应为粗体 
 ws.Cells [ 1 ， 1 ， 1 ，dt.Columns.Count] .Style.Horizo​​ntalAlignment = ExcelHorizo​​ntalAlignment.Center;  //   Aligmnet是中心 

通过这个，我能够合并标题，但我的目标是在标题顶部显示行。



我的目标是完成此任务。我没有限制使用EPPlus。如果它可以通过另一种方式完成，也欢迎它。



我很感激答案。谢谢。

解决方案

ws.Cells [1,1] .Value应该引用你的右上角单元格 - 成功出现在哪里的报告？如果没有，请尝试跳过合并。合并不会合并数据，而是使多个单元格显示为一个，这意味着您将丢失在这些合并单元格中填充的数据 - 在合并后输入您的值。最后，您是否意识到您手动覆盖了第一个单元格（可能是第一行）的数据？相反，为手册标题留一个空行：

 ws.Cells [  A2]。LoadFromDataTable（dt， true ）; 
 ws.Cells [ 1 ， 1 ， 1 ，dt.Columns.Count] .Merge =  true ;  //  合并列的开始和结束范围 
 ws.Cells [ 1 ， 1 ]。值=   report  //  标题名称 
 ws.Cells [  1 ， 1 ， 1 ，dt。 Columns.Count] .Style.Font.Bold =  true ;  //  字体应为粗体 
 ws.Cells [ 1 ， 1 ， 1 ，dt.Columns.Count] .Style.Horizo​​ntalAlignment = ExcelHorizo​​ntalAlignment.Center;  //   Aligmnet是中心 

创建一个包含所需样式的HTML表格，然后为文件提供 .xls 的文件扩展名。

Microsoft Excel（无论如何任何版本）将打开文件作为通常的Excel文档。



注意：使用简单的样式。

背景颜色，字体， border etc。

如果要创建真正的XLSX（Excel 2007/2010/2013）文档，可以使用 ClosedXML 来创建它。这是一个开源项目。



官方网站： http：//closedxml.codeplex .com / [ ^ ]



示例代码：

 使用 ClosedXML.Excel; 

  public   static  < span class =code-keyword> void  PublishExcel（）
 {
  var  workbook = 新 XLWorkbook（）; 
  var  worksheet = workbook.Worksheets.Add（ 样本表）; 
 worksheet.Cell（  A1）。Value =   Hello World！; 
 worksheet.Cell（ 2 ， 1 ）。SetValue（ 这是一个示例Excel文档）; 
 workbook.SaveAs（  HelloWorld.xlsx）; 
} 

通过ASP.NET发布

阅读文档：关闭XML - 如何在ASP.NET中提供Excel文件？ [ ^ ]



细胞合并：

阅读文档： CloseXML - 合并单元格 [ ^ ]



你可以做很多格式化等等。阅读项目文档

I am working with a web application. There,i am supposed to export data to excel. For that,i have made use of EPPlus.

I searched a lot but cant find out a way to add extra row at top of excel sheet.Like,i want row at top of header rows for the purpose of displaying company name(It will look like 3 or 4 rows are merged)

I have tried merging headers,but then it will display only single name(not all the headers)

The code is as below.

ws.Cells["A1"].LoadFromDataTable(dt, true);
ws.Cells[1, 1].Value = "report"// Heading Name
ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; //Merge columns start and end range
ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

Through this,i am able to merge headers,but my target is to display row at top of header.

My target is to accomplish this. I am not bounded to use EPPlus. If it can be done by another way than it is also welcome.

I appreciate the answers. Thanks.

解决方案

ws.Cells[1, 1].Value should reference your top right cell--is "report" appearing there successfully? If not, try skipping the merge. The merge doesn't combine the data, but rather makes multiple cells appear as one, which means you will appear to lose data that was populated in those merged cells--put your value in after the merge. Finally, are you aware that you're manually over-writing your first cell (probably first row) of data? Instead, leave a blank row for your manual header:

ws.Cells["A2"].LoadFromDataTable(dt, true);
ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; //Merge columns start and end range
ws.Cells[1, 1].Value = "report"// Heading Name
ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

Create a HTML table with all the styling you want, then give the file a file extension of ".xls".
Microsoft Excel (regardless of any version) will open the file as an usual Excel Document.

Note: Use simple styling.
background-color, font, border etc.

If you want to create real XLSX (Excel 2007/2010/2013) document, you can use ClosedXML to create it. It is an open source project.

Official Site: http://closedxml.codeplex.com/[^]

Sample Code:

using ClosedXML.Excel;

public static void PublishExcel()
{
    var workbook = new XLWorkbook();
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    worksheet.Cell(2, 1).SetValue("This is a sample Excel Document");
    workbook.SaveAs("HelloWorld.xlsx");
}

Publish through ASP.NET
Read Documentation: CloseXML - How do I deliver an Excel file in ASP.NET?[^]

For Cell Merging:
Read Documentation: CloseXML - Merging Cells[^]

You can do lots of Formatting, etc. Read the Documentation of the project.

这篇关于导出到Excel的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！