Reputation: 463
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.
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
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