将数据写入包含超过300列和60,000行的Excel工作表 [英] Writing data into excel sheet that contains more than 300 columns and 60,000 rows

问题描述

大家好，



我有一项任务，比如将数据写入包含超过300列和60,000行的Excel工作表。一个问题是我需要格式化像样式一样的单元格，条件格式化这些东西需要动态做。我不能使用Inteerop服务，因为我的服务器不支持这项服务。我尝试了很多方法，比如OpenXML，OpenXMlSAX方法。但是我遇到的一个问题是我用openxmlSAX方法不能对细胞进行造型。请在此建议我。 />




提前感谢您的答案。

Hi All,

I got one task like writing data into Excel sheet that contains more than 300 columns and 60,000 rows .One problem is i need to format the cells like styling,Conditional formatting these kind of things need to do on the fly. i cant use Inteerop services for this becuase my server doesnot support this services.I tried many methods like OpenXML,OpenXMlSAX approach.but one problem i was facing with openxmlSAX approach is cant do styling to the cells.Please suggest me in this.


Thanks in advance for the answers.

推荐答案

如果文件是那么大，你必须确保你通过正确分享样式等来编写优化文件。



你是否尝试在Excel中用数据做这么大的文件类似于你想要的样式。



我不知道Excel在最新版本中针对非常大的文件进行了多少优化，因为旧版本很容易生成永远要在Excel中加载的文件。我不记得问题是仅在2003年还是在2007年。对于非常大的数据，您肯定也需要使用64版本的Excel（2010或更高版本）。该版本在处理这些大文件方面肯定要好得多。



因此，如果你有非常大的文件，你必须首先确保生成的文件仍然可以在Excel中使用。当然，如果使用大量细胞的配方很多，那么你不可能同时拥有大量的行数和单行数，因为细胞数量会非常高。



正如其他人所提到的那样，并不是因为有人问起它有意义。您必须验证生成的文件是否可用于人们想要使用它。

If the file is that big, you have to ensure that you write an optimized file by properly sharing styles and such.

Have you tried to do such a big file in Excel with data and styling similar to what you want.

I don't know how much Excel has been optimized for very large files in recent version as for old version it was easy to generated files that take forever to load in Excel. I don't remember if the problem was only with 2003 or also 2007. For very large data, you surely need to use 64 version of Excel too (2010 or later). That version is surely much better at handling those large files.

Thus if you have really large files, you have to first ensure that the resulting file is still usable in Excel. For sure, you cannot have at the same time a huge amount of lines, of rows and sheets as the number of cells would be very high particularily if there would be a lot of formula using a lot of cells.

As others have mentionned, it is not because something was asked that it make sense. You have to verify that the resulting file is usable for what people want to do with it.

首先， Sergey Alexandrovich Kryukov [ ^ ]是对的。但是，如果你真的，真的，真的没有其他选择（客户永远是对的），请使用OpenXML格式，因为它提供了一种为单元格设置自定义格式的方法。请参阅：

如何创建Excel Open XML中的样式表 [ ^ ]

使用SDK 2.0和.NET格式化Excel [ ^ ]



最后，我需要警告你：目标文件会非常大！打开它可能需要几分钟。

First of all, Sergey Alexandrovich Kryukov[^] is right. But if you really, really, really do not have other option (customer is always right), use OpenXML format, because it provides a way to set custom format for cells. See:
How to create a stylesheet in Excel Open XML[^]
Formatted Excel using SDK 2.0 and .NET[^]

Finally, i need to warn you: destination file will be very BIG! It can take even few minutes to open it.

EPPlus [ ^ ]支持条件格式。例如：

https://epplus.codeplex.com/discussions/348196/ [ ^ ]

EPPlus[^] supports conditional formatting. For example:
https://epplus.codeplex.com/discussions/348196/[^]

这篇关于将数据写入包含超过300列和60,000行的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！