/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(
new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment =
this.sheet
.createDrawingPatriarch()
.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
/**
* Forms report in excel file. Sum weight. Sum volume etc.
*
* @param sheet Sheet where write the items
* @param c Container from where take the info
* @param lastRow last row after all items were written method uses lastRow + 2 to write report in
* next 2 rows after all items of the container were written
*/
private void setReport(Container c, int lastRow) {
String[] reportHeadings = {
"Суммарный вес", // 6
"Суммарный объем", // 7
"Остаток вес", // 8
"Остаток объем" // 9
};
double[] values = {
c.getWeight(),
c.getVolume(),
c.getWeightLimit() - c.getWeight(),
c.getVolumeLimit() - c.getVolume()
};
int dataCell = 5;
int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
lastRow++;
Row headings = outputSheet.createRow(lastRow);
lastRow += 2;
Row data = outputSheet.createRow(lastRow);
for (String report : reportHeadings) {
Cell heading = headings.createCell(dataCell);
heading.setCellValue(report);
Cell dataCellValue = data.createCell(dataCell);
dataCellValue.setCellValue(values[valuesIndex]);
dataCell++;
valuesIndex++;
}
}
@Override
public ModelAndView getReportTemplate() {
List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report");
int rownum = 0;
Row row0 = sheet.createRow(rownum++);
row0.createCell(0).setCellValue("Source Country");
row0.createCell(1).setCellValue("Service");
row0.createCell(2).setCellValue("Destination Country");
row0.createCell(3).setCellValue("Total Call Minutes");
for (MonthlyTraffic mt : monthlyTrafficReports) {
Row row = sheet.createRow(rownum++);
row.createCell(0).setCellValue(mt.getFromCountry());
row.createCell(1).setCellValue(mt.getServiceName());
row.createCell(2).setCellValue(mt.getToCountry());
row.createCell(3).setCellValue(mt.getMinutesOfCalls());
}
Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>();
parameterMap.put("excelBook", workbook);
ModelAndView modelAndView = new ModelAndView();
modelAndView.addAllObjects(parameterMap);
return modelAndView;
}
@Test
public void linksTest() throws IOException {
FileInputStream f =
new FileInputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("sheet1");
Iterator<Row> row = ws.iterator();
row.next();
while (row.hasNext()) {
Row r = row.next();
String linkname = r.getCell(0).getStringCellValue();
try {
driver.findElement(By.linkText(linkname)).click();
String acturl = driver.getCurrentUrl();
r.createCell(2).setCellValue(acturl);
String expurl = r.getCell(1).getStringCellValue();
if (acturl.equals(expurl)) {
r.createCell(3).setCellValue("passsed");
} else {
r.createCell(3).setCellValue("failed");
}
driver.navigate().back();
} catch (Exception e) {
r.createCell(3).setCellValue("links not found");
}
}
FileOutputStream f1 =
new FileOutputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
@Test
public void dataDrivernTest() throws IOException {
FileInputStream f =
new FileInputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("Sheet1");
Iterator<Row> row = ws.iterator();
row.next();
WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
wm.menu.register();
while (row.hasNext()) {
Row r = row.next();
rm1.contactInformation(r);
boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
if (result == true) {
r.createCell(12).setCellValue("Passed");
} else {
r.createCell(12).setCellValue("Failed");
}
driver.navigate().back();
}
FileOutputStream f1 =
new FileOutputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
/**
* make the excel file based on headers and contents which should have been set
*
* @param sheetName specified sheet name
* @return ExcelMaker itself
* @throws IllegalArgumentException only support String, Integer, Long, Double, Date, null as cell
* data.
*/
public ExcelMaker make(String sheetName) throws IllegalArgumentException {
// check if headers and contents set
if (!contextReady()) {
throw new IllegalArgumentException("invalid headers or contents");
}
Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);
// write the display headers as the 1st row in the sheet
int rowNum = 0;
Row headerRow = sheet.createRow(rowNum++);
if (displayHeaders != null) {
for (int cellNum = 0; cellNum < displayHeaders.size(); cellNum++) {
String header = displayHeaders.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
} else { // if display headers not set, use headers as default
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = headerRow.createCell(cellNum);
cell.setCellValue(header);
}
}
// parse each Object in content list and write as a row
for (Object content : contents) {
Map<String, Object> headerMap =
objectMapper.convertValue(content, new TypeReference<HashMap<String, Object>>() {});
Row row = sheet.createRow(rowNum++);
// only find properties that declared by headers
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
String header = headers.get(cellNum);
Cell cell = row.createCell(cellNum);
Object obj = headerMap.get(header);
if (obj == null) {
obj = "";
}
if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Integer) {
cell.setCellValue((Integer) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else {
throw new IllegalArgumentException("unsupported cell type");
}
}
}
return this;
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
protected void exportExtra(Row row, Item item) {
QiyiTop50 rank = (QiyiTop50) item;
Cell cell = row.createCell(2);
cell.setCellValue(rank.getActorsString());
cell = row.createCell(3);
cell.setCellValue(rank.getLastWeek().longValue());
cell = row.createCell(4);
cell.setCellValue(rank.getYesterDay().longValue());
cell = row.createCell(5);
cell.setCellValue(rank.getHistory().longValue());
}
// =================================================================================================================================
private int exportFrontEquipListReport_header(
List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
CellStyle style = getHeaderStyle(wb, (short) 11);
CellStyle style2 = getHeaderStyle(wb, (short) 10);
// 第一行列标题
Row row_subtype = sheet.createRow(rownum++);
Cell cell_subtype_0 = row_subtype.createCell(0);
cell_subtype_0.setCellValue("");
cell_subtype_0.setCellStyle(style);
sheet.setColumnWidth(0, 700);
Cell cell_subtype_1 = row_subtype.createCell(1);
cell_subtype_1.setCellValue("");
cell_subtype_1.setCellStyle(style);
sheet.setColumnWidth(1, 1800);
Cell cell_subtype_2 = row_subtype.createCell(2);
cell_subtype_2.setCellValue("");
cell_subtype_2.setCellStyle(style);
sheet.setColumnWidth(2, 4800);
// 第二行列标题
Row row_prod = sheet.createRow(rownum++);
Cell cell_prod_0 = row_prod.createCell(0);
cell_prod_0.setCellValue("序号");
cell_prod_0.setCellStyle(style2);
Cell cell_prod_1 = row_prod.createCell(1);
cell_prod_1.setCellValue("点位编号");
cell_prod_1.setCellStyle(style2);
Cell cell_prod_2 = row_prod.createCell(2);
cell_prod_2.setCellValue("点位名称");
cell_prod_2.setCellStyle(style2);
int cell_num = 3;
for (FrontEquipListReport_subtype subtype : subtypes) {
int start_cell_num = cell_num;
Cell cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellValue(subtype.getSubtype_name());
cell_subtype.setCellStyle(style);
for (FrontEquipListReport_prod prod : subtype.getProds()) {
if (start_cell_num != cell_num) {
cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellStyle(style);
}
Cell cell_prod = row_prod.createCell(cell_num);
cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
cell_prod.setCellStyle(style2);
cell_num++;
}
// 对子类型进行横向的单元格合并
sheet.addMergedRegion(
new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
}
return rownum;
}
private void writeModule(TrainingModule module) {
Row row = trainingSheet.createRow(++actualRow);
row.createCell(COL_MODULE_NUMBER).setCellValue(module.getNumber());
row.createCell(COL_MODULE).setCellValue(module.getName());
// for all topics write topics
Iterator<TrainingTopic> it = module.getTopics().iterator();
while (it.hasNext()) {
row = trainingSheet.createRow(++actualRow);
writeTopic(it.next(), row);
}
}
protected void createHeaderExtra(Row row, CellStyle style) {
Cell cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue("Actors");
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue("LastWeek");
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue("YesterDay");
cell = row.createCell(5);
cell.setCellStyle(style);
cell.setCellValue("History");
}
private Cell headInfo(CellStyle headerStyle, Row row) {
Cell cell = row.createCell(0);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.serialNumber"));
cell = row.createCell(1);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountNum"));
cell = row.createCell(STR_2);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountName"));
cell = row.createCell(STR_3);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.tranDate"));
cell = row.createCell(STR_4);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.currency"));
cell = row.createCell(STR_5);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.expenditure"));
cell = row.createCell(STR_6);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.income"));
cell = row.createCell(STR_7);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.balance"));
return cell;
}
private void fillRowWith(Row r, String[] data) {
Cell c = null;
for (String s : data) {
if (c == null) c = r.createCell(0);
else c = r.createCell(c.getColumnIndex() + 1);
try {
double d = Double.valueOf(s);
c.setCellValue(d);
} catch (NumberFormatException e) {
c.setCellValue(s);
}
}
}
@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
private static void createRow(
Sheet sheet,
Map<String, Object> rowData,
List<String> mapping,
List<CellStyle> styles,
int startRowNum) {
Row row = sheet.createRow(startRowNum);
for (int i = 0; i < mapping.size(); i++) {
String name = mapping.get(i);
Object obj = rowData.get(name);
Cell newCell = row.createCell(i);
CellStyle style = styles.get(i);
newCell.setCellStyle(style);
if (obj != null) {
if (obj instanceof Date) {
newCell.setCellValue((Date) obj);
} else if (obj instanceof BigDecimal) {
double dd = ((BigDecimal) obj).doubleValue();
newCell.setCellValue(dd);
} else {
newCell.setCellValue(obj.toString());
}
}
}
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
private void llenarCeldas(Row row, Object[] datos) {
int cellnum = 0;
// Columnas para datos de los partidos
for (int i = 0; i < datos.length; i++) {
Cell cell = row.createCell(cellnum++);
if (datos[i] == null) {
cell.setCellValue("");
} else if (datos[i] instanceof Date) {
cell.setCellValue((Date) datos[i]);
} else if (datos[i] instanceof Boolean) {
cell.setCellValue((Boolean) datos[i]);
} else if (datos[i] instanceof String) {
cell.setCellValue((String) datos[i]);
} else if (datos[i] instanceof Character) {
cell.setCellValue(String.valueOf(datos[i]));
} else if (datos[i] instanceof Double) {
cell.setCellValue((Double) datos[i]);
} else if (datos[i] instanceof Integer) {
cell.setCellValue((Integer) datos[i]);
}
}
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
public void writeData(Data data, boolean onlyNumbers) {
try {
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fOutputStream = new FileOutputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
@SuppressWarnings("rawtypes")
ArrayList<ArrayList> dataList = data.getData();
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j);
if (onlyNumbers) {
row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
} else {
row.getCell(j).setCellValue((String) dataList.get(i).get(j));
}
}
}
workbook.write(fOutputStream);
workbook.close();
fOutputStream.close();
workbook = null;
file = null;
sheet = null;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
public void fillData(List<Mobile> list, XSSFWorkbook workbook, XSSFSheet sheet) {
int j = 0;
int rowIndex = 2;
int stt = 1;
// XSSFCellStyle style1 = workbook.createCellStyle();
// style1.setFillForegroundColor(HSSFColor.AQUA.index);
// style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
// XSSFCreationHelper createHelper = workbook.getCreationHelper();
// XSSFCellStyle style2 = workbook.createCellStyle();
// style2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
ExcelUtil ex = new ExcelUtil(workbook);
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(rowIndex++);
j = 0;
row.createCell(j++).setCellValue(stt++);
row.createCell(j++).setCellValue(list.get(i).getWeb());
row.createCell(j++).setCellValue(list.get(i).getType());
row.createCell(j++).setCellValue(list.get(i).getBrand());
if (list.get(i).getPriceNumber() != null) {
ex.formatNumber(row, j++, list.get(i).getPriceNumber(), ex.number);
} else {
j++;
}
row.createCell(j++).setCellValue(list.get(i).getItemCode());
row.createCell(j++).setCellValue(list.get(i).getName());
row.createCell(j++).setCellValue(list.get(i).getModel());
row.createCell(j++).setCellValue(list.get(i).getStorage());
row.createCell(j++).setCellValue(list.get(i).getRam());
row.createCell(j++).setCellValue(list.get(i).getScreen());
row.createCell(j++).setCellValue(list.get(i).getCpu());
row.createCell(j++).setCellValue(list.get(i).getBackCamera());
row.createCell(j++).setCellValue(list.get(i).getFrontCamera());
row.createCell(j++).setCellValue(list.get(i).getOs());
row.createCell(j++).setCellValue(list.get(i).getBattery());
row.createCell(j++).setCellValue(list.get(i).getSim());
row.createCell(j++).setCellValue(list.get(i).getColor());
row.createCell(j++).setCellValue(list.get(i).getPromotion());
// Cell cell = row.createCell(j++);
// cell.setCellValue(list.get(i).getLastUpdate());
// cell.setCellStyle(style2);
ex.formatDate(row, j++, list.get(i).getLastUpdate(), ex.date);
row.createCell(j++).setCellValue(list.get(i).getLink());
}
}
public static Cell createCell(Row row, int index, Boolean value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
}
cell.setCellValue(value);
return cell;
}
public void generateReport() throws IOException {
System.out.println("Test Report Gen");
Sheet sheet = workbook.getSheet("sheet1");
try {
String strSql = "Select * from participant;";
Sql db = new Sql();
ResultSet rs = db.executeQuery(strSql);
ResultSetMetaData rsMeta = rs.getMetaData();
Row row = sheet.createRow(0);
Cell cell;
// get table columns which will act as title columns
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String colName = rsMeta.getColumnName(i);
int dataType = rsMeta.getColumnType(i);
cell = row.createCell(i - 1);
cell.setCellValue(colName);
}
int i = 1;
while (rs.next()) {
// create row to write data to
row = sheet.createRow(i);
// create cells for writing data to
for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
cell = row.createCell(c - 1);
String colName = rsMeta.getColumnName(c);
cell.setCellValue(rs.getString(colName));
}
i++;
}
JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
} catch (SQLException ex) {
ex.printStackTrace();
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
/// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
workbook.write(fileOut);
fileOut.close();
}
public static Cell createCell(Row row, int index, RichTextString value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
return cell;
}
cell.setCellValue(value);
return cell;
}
public static Cell createCell(Row row, int index, Date value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
return cell;
}
cell.setCellValue(DateUtils.formatDate(value, DateUtils.DateFormatType.DATE_FORMAT_STR_CHINA));
return cell;
}
