Sham Yemul
Sham Yemul

Reputation: 463

npoi date format

I am using NPOI 1.2.5.0 , Visual Studio 2008. There is a date column in the spreadsheet. I

observed that, if I open file in Microsoft Excel, set size of column to autoset , save it ,

it goes fine.I can't use this solution, because there is no excel installed on Production

PC.

Otherwise, the date column shows #### instead of date, when I process other logic/coding

using NPOI and save this file. And I open excel, it says file is corrupted, needs

recovered, data may be lost. If I click "yes", it does recover file and show everything correct.

I found that this can be solved, if I open file autoset date column save excel using

Microsoft Excel. After processing using NPOI .net code, it does open ok.

  1. So I decided to do autoset thr' code using NPOI, it does not increase width of date

column, instead it shows numbers there.

I had written following code to set Date Format, but it is displaying number instead of

date. I tried autoset code before OR after this block, no change.

for (int i = 0; i <= nTotalRows; i++)
            {
                HSSFCellStyle cellStyle = (HSSFCellStyle)templateWorkbook.CreateCellStyle();
                HSSFCell Cell = (HSSFCell)exlSheet.GetRow(i).GetCell(nCheckColumn);
                //(HSSFCell)exlSheet.Sheet.get .SetDefaultColumnStyle(nCheckColumn,
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("mm-dd-yy");
                Cell.CellStyle = cellStyle;
                MemoryStream ms = new MemoryStream();
                templateWorkbook.Write(ms);
                ms.Close();
                FileStream fs1 = new FileStream(strFilePath, FileMode.Create);
                templateWorkbook.Write(fs1);
                fs1.Close();
            }

Please suggest a solution for this. I was searching for Getcolumn to set Dataformat to

complete column instead of cell.

sham

Upvotes: 2

Views: 4845

Answers (1)

Jammy
Jammy

Reputation: 789

I use this to auto set the column widths:

HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(x);
sheet.AutoSizeColumn(y);

where x is the sheet number and y is the column number. This will at least set the width so excel does not show ###

Upvotes: 1

Related Questions