/// <summary>
/// Function to add a new column to the Excel worksheet
/// </summary>
/// <param name="columnHeader">The column header to be added</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the column header</param>
public void AddColumn(String columnHeader, ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(0); //0 because header is always in the first row
int lastCellNum = row.LastCellNum;
if (lastCellNum == -1)
{
lastCellNum = 0;
}
HSSFCell cell = (HSSFCell)row.CreateCell(lastCellNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(columnHeader);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}
private HSSFCellStyle ApplyCellStyle(HSSFWorkbook workbook,
ExcelCellFormatting cellFormatting)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (cellFormatting.Centred)
{
cellStyle.Alignment = HorizontalAlignment.Center;
}
cellStyle.FillForegroundColor = cellFormatting.BackColorIndex;
cellStyle.FillPattern = FillPattern.SolidForeground;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = cellFormatting.FontName;
font.FontHeightInPoints = cellFormatting.FontSize;
if (cellFormatting.Bold)
{
font.Boldweight = (short)FontBoldWeight.Bold;
}
font.Color = cellFormatting.ForeColorIndex;
cellStyle.SetFont(font);
return(cellStyle);
}
/// <summary>
/// Function to set the specified value in the cell identified by the specified row number and column header
/// </summary>
/// <param name="rowNum">The row number of the cell</param>
/// <param name="columnHeader">The column header of the cell</param>
/// <param name="value">The value to be set in the cell</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the cell</param>
public void SetValue(int rowNum, String columnHeader, String value,
ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(0); //0 because header is always in the first row
int columnNum = -1;
String currentValue;
for (int currentColumnNum = 0;
currentColumnNum < row.LastCellNum; currentColumnNum++)
{
currentValue = GetCellValue(worksheet, row, currentColumnNum);
if (currentValue.Equals(columnHeader))
{
columnNum = currentColumnNum;
break;
}
}
if (columnNum == -1)
{
throw new FrameworkException("The specified column header " + columnHeader + " is not found in the sheet \"" + DatasheetName + "\"!");
}
else
{
row = (HSSFRow)worksheet.GetRow(rowNum);
HSSFCell cell = (HSSFCell)row.CreateCell(columnNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(value);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}
}
/// <summary>
/// Function to set the specified value in the cell identified by the specified row and column numbers
/// </summary>
/// <param name="rowNum">The row number of the cell</param>
/// <param name="columnNum">The column number of the cell</param>
/// <param name="value">The value to be set in the cell</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the cell</param>
public void SetValue(int rowNum, int columnNum, String value,
ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(rowNum);
HSSFCell cell = (HSSFCell)row.CreateCell(columnNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(value);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}