/**
* Constructor HSSF - given a filename this outputs a sample sheet with just a Set of rows/cells.
*
* @param filename
* @param Write
* @exception IOException
*/
public HSSF(String filename, bool Write) {
short rownum = 0;
FileOutputStream out = new FileOutputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.CreateSheet();
HSSFRow r = null;
HSSFCell c = null;
HSSFCellStyle cs = wb.CreateCellStyle();
HSSFCellStyle cs2 = wb.CreateCellStyle();
HSSFCellStyle cs3 = wb.CreateCellStyle();
HSSFFont f = wb.CreateFont();
HSSFFont f2 = wb.CreateFont();
f.SetFontHeightInPoints((short) 12);
f.SetColor((short) 0xA);
f.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
f2.SetFontHeightInPoints((short) 10);
f2.SetColor((short) 0xf);
f2.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.SetFont(f);
cs.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cs2.SetBorderBottom(HSSFCellStyle.BORDER_THIN);
cs2.SetFillPattern((short) 1); // Fill w fg
cs2.SetFillForegroundColor((short) 0xA);
cs2.SetFont(f2);
wb.SetSheetName(0, "HSSF Test");
for (rownum = (short) 0; rownum < 300; rownum++) {
r = s.CreateRow(rownum);
if ((rownum % 2) == 0) {
r.SetHeight((short) 0x249);
}
// r.SetRowNum(( short ) rownum);
for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
c.SetCellValue(
rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
if ((rownum % 2) == 0) {
c.SetCellStyle(cs);
}
c = r.CreateCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
c.SetCellValue("TEST");
s.SetColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
if ((rownum % 2) == 0) {
c.SetCellStyle(cs2);
}
} // 50 Chars divided by 1/20th of a point
}
// draw a thick black border on the row at the bottom using BLANKS
rownum++;
rownum++;
r = s.CreateRow(rownum);
cs3.SetBorderBottom(HSSFCellStyle.BORDER_THICK);
for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_BLANK);
// c.SetCellValue(0);
c.SetCellStyle(cs3);
}
s.AddMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
s.AddMergedRegion(
new Region(
(short) 100, (short) 100,
(short) 110, (short) 110));
// end draw thick black border
// Create a sheet, Set its title then delete it
s = wb.CreateSheet();
wb.SetSheetName(1, "DeletedSheet");
wb.RemoveSheetAt(1);
// end deleted sheet
wb.Write(out);
out.close();
}
/**
* Method main
*
* <p>Given 1 argument takes that as the filename, inputs it and dumps the cell values/types out
* to sys.out
*
* <p>given 2 arguments where the second argument Is the word "Write" and the first Is the
* filename - Writes out a sample (test) spReadsheet (see public HSSF(String filename, bool
* Write)).
*
* <p>given 2 arguments where the first Is an input filename and the second an output filename
* (not Write), attempts to fully Read in the spReadsheet and fully Write it out.
*
* <p>given 3 arguments where the first Is an input filename and the second an output filename
* (not Write) and the third Is "modify1", attempts to Read in the spReadsheet, deletes rows 0-24,
* 74-99. Changes cell at row 39, col 3 to "MODIFIED CELL" then Writes it out. Hence this Is
* "modify test 1". If you take the output from the Write test, you'll have a valid scenario.
*
* @param args
*/
public static void main(String[] args) {
if (args.Length < 2) {
/* try
{
HSSF hssf = new HSSF(args[ 0 ]);
Console.WriteLine("Data dump:\n");
HSSFWorkbook wb = hssf.hssfworkbook;
for (int k = 0; k < wb.GetNumberOfSheets(); k++)
{
Console.WriteLine("Sheet " + k);
HSSFSheet sheet = wb.GetSheetAt(k);
int rows = sheet.GetPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.GetPhysicalRowAt(r);
int cells = row.GetPhysicalNumberOfCells();
Console.WriteLine("ROW " + row.GetRowNum());
for (int c = 0; c < cells; c++)
{
HSSFCell cell = row.GetPhysicalCellAt(c);
String value = null;
switch (cell.GetCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = "NUMERIC value="
+ cell.GetNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = "STRING value="
+ cell.GetStringCellValue();
break;
default :
}
Console.WriteLine("CELL col="
+ cell.GetCellNum()
+ " VALUE=" + value);
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}*/
} else if (args.Length == 2) {
if (args[1].ToLower().Equals("Write")) {
Console.WriteLine("Write mode");
try {
long time = System.currentTimeMillis();
HSSF hssf = new HSSF(args[0], true);
System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
} catch (Exception e) {
e.printStackTrace();
}
} else {
Console.WriteLine("ReadWrite test");
try {
HSSF hssf = new HSSF(args[0]);
// HSSFStream hssfstream = hssf.hssfstream;
HSSFWorkbook wb = hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[1]);
// HSSFCell cell = new HSSFCell();
// cell.SetCellNum((short)3);
// cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.SetCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.Write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
} else if ((args.Length == 3) && args[2].ToLower().Equals("modify1")) {
try // delete row 0-24, row 74 - 99 && Change cell 3 on row 39 to string "MODIFIED CELL!!"
{
HSSF hssf = new HSSF(args[0]);
// HSSFStream hssfstream = hssf.hssfstream;
HSSFWorkbook wb = hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[1]);
HSSFSheet sheet = wb.GetSheetAt(0);
for (int k = 0; k < 25; k++) {
HSSFRow row = sheet.GetRow(k);
sheet.RemoveRow(row);
}
for (int k = 74; k < 100; k++) {
HSSFRow row = sheet.GetRow(k);
sheet.RemoveRow(row);
}
HSSFRow row = sheet.GetRow(39);
HSSFCell cell = row.GetCell((short) 3);
cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
cell.SetCellValue("MODIFIED CELL!!!!!");
// HSSFCell cell = new HSSFCell();
// cell.SetCellNum((short)3);
// cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.SetCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.Write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}