Archive for category APACHI POI

Apache POI HSSFWorkbook

 

/*

create dynamic excel format text,double,date

*/

package com.ge.scott.service.project.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ProductTestExcel {

public static HSSFWorkbook createDynamicExcel() {

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(“new sheet”);

// Create a row and put some cells in it. Rows are 0 based.

HSSFCellStyle dateStyle = getDateCellFormat(wb);
HSSFCellStyle doubleStyle = getNumberCellStyle(wb);
HSSFCellStyle textStyle = getTextCellStyle(wb);

int rowid = 0;
int cellid = 0;

for (int i = 0; i < 100; i++) {
HSSFRow row = sheet.createRow(rowid);

createDateCell(row, cellid, dateStyle, new Date());
cellid++;
createDoubleCell(row, cellid, doubleStyle, new Double(20.18));
cellid++;
createTextCell(row, cellid, textStyle, “java”);

cellid++;

 

cellid = 0;
rowid++;

}

try {

File file = createDirectory();
FileOutputStream out = new FileOutputStream(file, false);

// Write the workbook in file system
wb.write(out);
out.close();
System.out.println(“Report.xls written successfully”);

} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return wb;

}

/**
*
* @param row
* @param cellid
* @param cellStyle
* @param value
*/
public static void createTextCell(HSSFRow row, int cellid, HSSFCellStyle cellStyle, String value) {

HSSFCell cell = row.createCell(cellid);
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
System.out.println(“createNumberCell cellid:” + cellid);

}

/**
*
* @param row
* @param cellid
* @param cellStyle
* @param date
*/
public static void createDateCell(HSSFRow row, int cellid, HSSFCellStyle cellStyle, Date date) {

HSSFCell cell = row.createCell(cellid);
cell.setCellValue(date);
cell.setCellStyle(cellStyle);
System.out.println(“createDateCell cellid:” + cellid);
}

/**
*
* @param row
* @param cellid
* @param cellStyle
* @param value
*/
public static void createDoubleCell(HSSFRow row, int cellid, HSSFCellStyle cellStyle, Double value) {
HSSFCell cell = row.createCell(cellid);
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
System.out.println(“createNumberCell cellid:” + cellid + ” deger:” + value);

}

/**
*
* @param wb
* @return
*/

public static HSSFCellStyle getDateCellFormat(HSSFWorkbook wb) {
HSSFCellStyle cellStyle;
cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(“m/d/yy”));
return cellStyle;
}

/**
*
* @param wb
* @return
*/

public static HSSFCellStyle getNumberCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle;
cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(“#,##0.00”));
return cellStyle;
}

/**
*
* @param wb
* @return
*/

public static HSSFCellStyle getTextCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle;
cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(“@”));
return cellStyle;
}

/**
*
* @return
* @throws IOException
*/
private static File createDirectory() throws IOException {
File file = new File(“C:\\report”);
if (!file.exists()) {
file.mkdir();
}

file = new File(“C:\\report\\report.xls”);
if (!file.exists()) {
file.createNewFile();
}
return file;
}

}

, ,

Leave a comment