Comments on Leniel Macaferi's blog: NPOI with Excel Table and dynamic Chart

Oi Leniel. Muito bom o NPOI. Porem pode me ajudar ...

2011-10-12T03:22:24.719-03:00

Oi Leniel.
Muito bom o NPOI.
Porem pode me ajudar com o ImportXlsToDataTable.
No sei código esta assim:

DataTable dt = new DataTable(); for (int j = 0; j < 5; j++) { dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString()); }

Se mudo para j < 70 dobra a planilha e sobreescreve os dados.
Também numa planilha normal, depois do Z viria AA,..AZ, BA,...BZ...
Porem aparece carácteres estranhos no meu computador. Talvez por meu sistema estar em Japonês. Não sei certo! Vou rodar em uma maquina em Português para ver se não é erro meu.
O NPOI só pode gerenciar até 4000 células?
Como podemos fixar estes problemas.
Abraços,
ocaccy

Would you like to share that how do you create a c...

2011-06-30T02:30:48.977-03:00

Would you like to share that how do you create a chart with NPOI programmatically?

Very useful information, thanks

2011-06-23T13:28:33.544-03:00

Very useful information, thanks

Great article. One thing you have not mentioned i...

2011-06-18T17:11:36.795-03:00

Great article. One thing you have not mentioned is the cell format or row format. Is there a way to handle it?

Let's say column C is set with yellow bg when value of column A is 1000....

Thanks

Thank you for this article. What happens, though, ...

2011-06-10T01:12:52.805-03:00

Thank you for this article. What happens, though, if you have hundreds or thousands of rows of data from a database? How would one go about handling that scenario? Would you have to declare each individual row? (I've posted a problem at stackoverflow: http://stackoverflow.com/questions/6259509/error-attempting-to-use-npoi-to-fill-excel-template)

Anonymous, I played with ExcelPackage right now. ...

2010-07-29T21:07:02.811-03:00

Anonymous,

I played with ExcelPackage right now.

ExcelPackage isn't updated since 2007. From ExcelPackage Home Page you can get an idea about how it's doing with formula recalculation. Read this: How do I get Excel to recalculate my formula on File-Open?.

To test it I used the sample app I wrote for the post Creating Excel spreadsheets .XLS and .XLSX in C#. You can get it here.

I experienced that same problem. Excel didn't update the formula when the workbook was opened.

What I did to fix:

- I opened the Excel template located at C:\ExcelWriterMvcProject\Content\ExcelPackageTemplate.xlsx and added a simple formula (=A1+A5) in Row 3 Column 4 (cell D3).

- I used the workaround described in ExcelPackage home, that is, in my C# code I wrote this line:

// Removing value from cell so that Excel recalculates the formula.
myWorksheet.Cell(3, 4).RemoveValue();

I then ran the app and got the expected result. Cell D3 had a value of 177. Cell A1 has a value of 100 and cell A5's value is 77. This last value I set in C# code. This shows that the recalculation works in this simple case.

As for the Excel table case shown in this very post, I tried to run the sample app writing code to work with ExcelPackage but it was unsuccessful. The rows get added to the table but the Totals row don't get updated when you open the workbook in Excel. I wrote this in my C# code:

// This is the Totals row
myWorksheet.Cell(12, 3).RemoveValue();
myWorksheet.Cell(12, 4).RemoveValue();
myWorksheet.Cell(12, 5).RemoveValue();
myWorksheet.Cell(12, 6).RemoveValue();

Even with this the Totals row wasn't updated.

So, answering your question:

Any way to do the recalculation with ExcelPackage?

No way when you open Excel at first. The added rows are hidden and the Totals row isn't updated at all. If you remove the filter on the Players column (with Select All) the Totals row get updated. If you don't mind this is still a way of getting this to work.

I haven't tried this Force recalculation of formulas but it may be of some value to you if you're willing to work with ExcelPackage source code.

Leniel

Leniel, Great post! Any way to do the recalculati...

2010-07-29T16:10:24.906-03:00

Leniel,

Great post! Any way to do the recalculation with ExcelPackage?

Hi PierrOz, You can for sure create a spreadsheet...

2010-03-07T19:44:13.606-03:00

Hi PierrOz,

You can for sure create a spreadsheet from scratch.

The example shown in this post is based on a case in which the user was using a template.

You can see how to create a spreadsheet from scratch looking at NPOI samples package - you can download the samples package at http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19351#DownloadId=70100.

Take a look at the project called CreateEmptyExcelFile.

All the best,

Leniel Macaferi

this example is great ,thanks. However you need to...

2010-03-05T08:23:55.546-03:00

this example is great ,thanks. However you need to use a primary spreadsheet as a template. Isn't it possible to create a new spreadsheet "from scratch" without any template ?