excel processing in c#_问答_开发者_运维开发者技术经验分享

开发者 https://www.devze.com 2023-01-20 03:11 出处：网络

I am developing a job application. Each job generates an excel file. I will have 50 parallel jo开发者_开发技巧b. So 50 excel files will be generated parallelly.

相关专题：.net-3.5 excel

I am developing a job application. Each job generates an excel file. I will have 50 parallel jo开发者_开发技巧b. So 50 excel files will be generated parallelly.

I am using C#3.5 and Excel 2003. The problem is I am unable to instantiate more Excel objects. I am getting COMException. So do I need to create excel processing only one at a time? Do you have any solution for this?

Pls help me.

Edit:

The excel genration doesn't need user interaction. Its a scheduled job.

I am generating Excel (xls). I need to do formatting and coloring in excel, so I can't use csv. Now I have synchronized the code, so at a time only one job processes Excel. But its taking too much time, since only one excel processing at a time.

Any kind of Excel pooling logic will help? Please direct me.

To do this you would need to start 50 instances of Excel, that would not work.

You have 3 options:

Use Open XML SDK 2.0 for Microsoft Office. This allows you to write to an excel file as if it was an xml file. No need to start Excel.
Use SharePoint Excel Services. This allows you to do server side Excel processing. No need to start Excel. The problem with this is that the SharePoint version that includes Excel Services is expensive.
Process the files one at a time, as you suggested.

ooooh i once have that kind of problem b4.

my solution is i use html excel. honestly, its kinda stupid solution, but it work pretty well and very easy to implement.

1 create html template a bit like this

<html>
<body>
name - <div>$<name>$</div>
</body>
</html>

2 read your template as string

System.IO.StreamReader stream = new System.IO.StreamReader("");
string template = stream.ReadToEnd();
template = template.Replace("$<name>$", "John");

3 then save your string as .xls

**to create a template sheets is very easy.

1 create excel template like u normally do in excel

2 in the cell u want to replace yout value type in $$name$$ *note that above i use $<'value'>$. but if we do this way i suggest u cahnge to $$name$$ becus for < and > excel gonna do HtmlEncode for us

3 u can create many sheets as u like

4 'Save as' .mht

5 then later change file.mht to .xls and string.Replace("$$name$$", "John");

Does the generation of 50 excel files require user interaction?
If not, you can create a nightly job that generates files.

If it is a plain text kind of a format(or CSV), you don't need Excel.
Also, you can use one excel instance to generate all the files, one after the other.