Last edited Dec 23, 2012 at 6:57 PM by tonyqus, version 14

Comments

Jingles Aug 17, 2012 at 4:27 AM 
Hi,

Great library! I found one bug though, when extracting string values from a cell, this string "<c" seems act as a delimiter and chops of the remaining data.

For example, if a cell contains this the following data, "for(int i = 0; i<chars; i++);", if you try to retrieve this cell's value, (worksheet.GetRow(row).GetCell(col).StringCellValue) the returned result would be "for(int i = 0; i". As you can see the remaining content is ignored.

If you were to add a space between '<' and 'c' so the cell content looks looks like this "for(int i = 0; i< chars; i++);", it returns everything in the cell.

grahamt Dec 6, 2011 at 10:25 AM 
Hi

Thanks for that. Great library! I made a slight modification to the wrapper code above as I was getting a corrupted file.

public ExcelApp WriteSave(string filePath)
{
if (File.Exists(filePath)) File.Delete(filePath);
FileStream file = new FileStream(filePath, FileMode.Create);
_hssfworkbook.Write(file);
file.Flush();
file.Close();
return this;
}

shastriakhil May 2, 2011 at 12:15 PM 
I've created a wrapper

public class ExcelApp
{

private Sheet _currentSheet;
HSSFWorkbook _hssfworkbook;
public ExcelApp CreateFile(string sheetSubject="")
{
_hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Algorhythm Tech Pvt Ltd";
_hssfworkbook.DocumentSummaryInformation = dsi;

//////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = sheetSubject;
_hssfworkbook.SummaryInformation = si;
return this;
}
public ExcelApp CreateSheet(string sheetName = "Sheet1")
{
_currentSheet = _hssfworkbook.CreateSheet(sheetName);
return this;
}
public ExcelApp WriteSave(string filePath)
{
if(File.Exists(filePath)) File.Delete(filePath);
FileStream file = new FileStream(filePath, FileMode.Create);
_hssfworkbook.Write(file);
return this;
}
public ExcelApp GetSheet(string name)
{
_currentSheet = _hssfworkbook.GetSheet(name);
return this;
}
private Row _row;
public ExcelApp CreateRow(int rownum)
{
_row = CurrentSheet.CreateRow(rownum);
return this;
}
private Cell _cell;
public ExcelApp CreateCell(int cellnum)
{
_cell = _row.CreateCell(cellnum);
return this;
}
public ExcelApp SetCellValue(string val)
{
_cell.SetCellValue( val);
return this;
}
public ExcelApp SetCellValue(DateTime val)
{
_cell.SetCellValue(val);
return this;
}
public ExcelApp SetCellValue(double val)
{
_cell.SetCellValue(val);
return this;
}
public ExcelApp SetCellValue(RichTextString val)
{
_cell.SetCellValue(val);
return this;
}
public ExcelApp SetCellValue(bool val)
{
_cell.SetCellValue(val);
return this;
}
public Row CurrentRow { get { return _row; } }
public Cell CurrentCell { get { return _cell; } }
public CellStyle CurrentCellStyle
{
get { return _cellStyle; }
}

public Sheet CurrentSheet
{
get { return _currentSheet; }
}

private CellStyle _cellStyle;
/// <summary>
/// <example>
/// <b>// we style the second cell as a date (and time). It is important to Create a new cell style from the workbook
/// // otherwise you can end up modifying the built in style and effecting not only this cell but other cells.
/// </b>
/// CellStyle cellStyle = hssfworkbook.CreateCellStyle();
///
/// // Perhaps this may only works for Chinese date, I don't have english office on hand
/// // cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("[$-409]h:mm:ss AM/PM;@");
///
/// cell.CellStyle=cellStyle;
/// </example>
/// </summary>
/// <returns></returns>
public ExcelApp CreateCellStyle()
{
_cellStyle= _hssfworkbook.CreateCellStyle();
return this;
}

public ExcelApp OpenExcelFile(string filePath)
{
_hssfworkbook = new HSSFWorkbook(OpenFileStream(filePath));
_currentSheet = _hssfworkbook.GetSheetAt(0);
_row = CurrentSheet.GetRow(0);
_cell = _row.GetCell(0);
return this;
}

public ExcelApp GetCell(int col,int row)
{
_row = CurrentSheet.GetRow(row);
_cell = _row.GetCell(col);
return this;
}

private Stream OpenFileStream(string filePath)
{
return new FileStream(filePath, FileMode.Open);
}
public ExcelApp SetRowHeight(short height)
{
_row.Height = height;
return this;
}
public ExcelApp SetColWidth(short col,int width)
{
CurrentSheet.SetColumnWidth(col,width);
return this;
}
public string GetStringTex (int col,int row)
{
return _currentSheet.GetRow(row).GetCell(col).StringCellValue;
}
public DateTime GetDateTex(int col, int row)
{
return _currentSheet.GetRow(row).GetCell(col).DateCellValue;
}
public RichTextString GetRichTextTex(int col, int row)
{
return _currentSheet.GetRow(row).GetCell(col).RichStringCellValue;
}
public double GetdoubleTex(int col, int row)
{
return _currentSheet.GetRow(row).GetCell(col).NumericCellValue;
}
}