public double getRendimientoAcumulado() throws XLSException {
if (rendimiento != null && rendimiento != 0.0) {
return rendimiento;
}
FileInputStream fileInputStream;
try {
fileInputStream = new FileInputStream(RUTA_ARCHIVO + "\\Rendimiento.xls");
} catch (FileNotFoundException e) {
return 0.0;
}
HSSFWorkbook workbook;
try {
workbook = new HSSFWorkbook(fileInputStream);
} catch (IOException e) {
e.printStackTrace();
throw new XLSException();
}
HSSFSheet worksheet = workbook.getSheet(PESTANA);
HSSFRow row = worksheet.getRow(1);
HSSFCell celdaC2 = row.getCell(convert('c'));
// Buscar en F2, la cantidad total de Pycks
if (celdaC2 == null) {
return 0.0;
}
rendimiento = celdaC2.getNumericCellValue();
return rendimiento;
}
private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
private File writeSingleXLS() throws ReportException {
File file = null;
HSSFWorkbook wb = null;
OutputStream out = null;
try {
file = getReportFile();
wb = new HSSFWorkbook();
ReportDefineFrm definedReport = executeFrm.getDefinedReport();
String sheetName = definedReport.getReportName();
HSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDisplayGridlines(false); // 不显示网格线
HSSFCellStyle dataStyle = createDataStyle(wb);
HSSFCellStyle headerStyle = createHeaderStyle(wb);
if (isFirstFile) {
writeDataHeader(sheet, headerStyle);
}
writeData2Excel(sheet, dataStyle, headerStyle);
autoReSizeSheet(sheet);
out = new FileOutputStream(file);
wb.write(out);
isFirstFile = false;
} catch (Throwable ex) {
Logger.logError(ex);
throw new ReportException(ex.getMessage());
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException ex) {
Logger.logError(ex);
}
}
return file;
}
public HSSFCellStyle createLastCellStyle(boolean white) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if (white) {
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
} else {
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
}
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
public void atualizar(Jogo jogo) {
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == jogo.getID()) {
HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);
novaRow.createCell(1).setCellValue(jogo.getNome());
novaRow.createCell(2).setCellValue(jogo.getConsole());
novaRow.createCell(3).setCellValue(jogo.getPrecoCompra());
novaRow.createCell(4).setCellValue(jogo.getPrecoVenda());
novaRow.createCell(5).setCellValue(jogo.getQuantidade());
novaRow.createCell(6).setCellValue(jogo.getGenero());
FileOutputStream nFile = null;
try {
nFile = new FileOutputStream("Jogos.xls");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
this.planilha.write(nFile);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
nFile.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* 测试单元格样式
*
* @author David
* @param wb
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions =
new CellRangeAddressList(
cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
public static void read(int rowxi, int rowxj) {
try {
POIFSFileSystem fs;
fs =
new POIFSFileSystem(
new FileInputStream(
"F:\\工作\\1_Java资料\\5_workspace\\hhw\\poi\\src\\com\\hhw\\poi\\temp1.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
for (int i = rowxi - 1; i < rowxj; i++) {
String name = sheet.getRow(i).getCell(0).getStringCellValue();
String bankName = name.substring(0, name.indexOf("(") - 1);
String bankNo = name.substring(name.indexOf("(") + 1, name.indexOf(")"));
sheet.getRow(i).getCell(13).setCellType(Cell.CELL_TYPE_STRING);
String bin = sheet.getRow(i).getCell(13).getStringCellValue();
String cardType = sheet.getRow(i).getCell(15).getStringCellValue();
System.out.println(bankName + "," + bankNo + "," + bin + "," + cardType);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
* и если он отсутствует то регистрирует его.
*
* @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
* @param font характеристики требуемого фонта.
* @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
*/
public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
HSSFFont f =
wb.findFont(
font.getBoldWeight(),
colorId,
font.getFontHeight(),
font.getFontName(),
font.isItalic(),
font.isStrikeout(),
font.getTypeOffset(),
font.getUnderline());
if (f == null) {
f = wb.createFont();
f.setBoldweight(font.getBoldWeight());
f.setCharSet(font.getCharSet());
f.setColor(colorId);
f.setFontHeight(font.getFontHeight());
f.setFontName(font.getFontName());
f.setItalic(font.isItalic());
f.setStrikeout(font.isStrikeout());
f.setTypeOffset(font.getTypeOffset());
f.setUnderline(font.getUnderline());
}
return f;
}
public Collection<Tree> read(final String fileName) throws IOException {
List<Tree> result = new ArrayList<Tree>();
InputStream file = getClass().getResourceAsStream(fileName);
if (file == null) {
throw new IllegalArgumentException("File " + fileName + " not found");
}
try {
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
// Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // ignore column title
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
TreeBuilder reader = new TreeBuilder(row);
Tree tree = reader.buildTree();
result.add(tree);
System.out.println(tree.toString());
}
} finally {
file.close();
}
return result;
}
@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;
}
/**
* Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
* there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
* span multiple sheets. They are are only defined within a sheet, and thus they do not have a
* sheet index field (only row and column range fields).<br>
* So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
* does not allow matches across sheets.</br>
*
* <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
* allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
* behaviour is shown by this test.
*
* <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
* (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
* Excel or POI) clears the flag.<br>
*
* <ol>
* <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
* <li>Sheet3 was deleted.
* <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
* <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
* <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
* Del).
* <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
* </ol>
*
* Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
* each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
* The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
* from Sheet1 (but not clear the 'shared formula' flags.<br>
* There are other variations on this theme to create the same effect.
*/
public void testSpuriousSharedFormulaFlag() {
long actualCRC =
getFileCRC(
HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC) {
System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb =
HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
HSSFSheet s = wb.getSheetAt(0); // Sheet1
String cellFormula;
cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
// the problem is not observable in the first row of the shared formula
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
// but the problem is observable in rows 2,3,4
cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.equals("\"second formula\"")) {
throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
}
/**
* 根据模板初始化样式 注意：module.xls模板文件跟该类同一路径
*
* @param wb
* @return
*/
private HashMap<String, HSSFCellStyle> initStyles(HSSFWorkbook wb) {
HashMap<String, HSSFCellStyle> ret = new HashMap<String, HSSFCellStyle>();
try {
ClassPathResource res =
new ClassPathResource("module.xls", this.getClass()); // 注意：module.xls模板文件跟该类同一路径
InputStream is = res.getInputStream();
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook src = new HSSFWorkbook(fs);
HSSFSheet sheet = src.getSheetAt(0);
buildStyle(wb, src, sheet, 0, ret, "TITLE"); // 标题样式
buildStyle(wb, src, sheet, 1, ret, "SUB_TITLE"); // 副标题样式
buildStyle(wb, src, sheet, 2, ret, "SUB_TITLE2"); // 副标题2样式
buildStyle(wb, src, sheet, 4, ret, "TABLE_HEADER"); // 表头样式
buildStyle(wb, src, sheet, 5, ret, "STRING"); // 字符串单元格样式
buildStyle(wb, src, sheet, 6, ret, "INT"); // 整数单元格样式
buildStyle(wb, src, sheet, 7, ret, "D2"); // 2位小数单元格样式
buildStyle(wb, src, sheet, 8, ret, "D3"); // 3位小数单元格样式
buildStyle(wb, src, sheet, 10, ret, "STRING_C"); // 字符串单元格样式（带背景色）
buildStyle(wb, src, sheet, 11, ret, "INT_C"); // 整数单元格样式（带背景色）
buildStyle(wb, src, sheet, 12, ret, "D2_C"); // 2位小数单元格样式（带背景色）
buildStyle(wb, src, sheet, 13, ret, "D3_C"); // 3位小数单元格样式（带背景色）
buildStyle(wb, src, sheet, 15, ret, "RED_BG"); // 红色单元格背景
buildStyle(wb, src, sheet, 16, ret, "YELLOW_BG"); // 黄色单元格背景
buildStyle(wb, src, sheet, 17, ret, "GREEN_BG"); // 绿色单元格背景
} catch (Exception e) {
e.printStackTrace();
}
return ret;
}
public void export(OutputStream outputStream) {
XLSTransformer transformer = new XLSTransformer();
InputStream is = null;
try {
is = new BufferedInputStream(new FileInputStream(template));
HSSFWorkbook workbook = transformer.transformXLS(is, dataMap);
workbook.write(outputStream);
outputStream.flush();
} catch (FileNotFoundException e) {
throw new RuntimeException("模版文件没有找到!", e);
} catch (IOException e) {
e
.printStackTrace(); // To change body of catch statement use File | Settings | File
// Templates.
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
throw new RuntimeException("输入流关闭失败!", e);
}
}
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException("输出流关闭失败!", e);
}
}
}
}
/**
* 导出Excel(多工作表)
*
* @param title 文件名
* @param creator 创建人
* @param tableDataLst 各工作格数据(注意：每个tableData要设置sheet名称，否则按默认呈现)
* @return void <style name="dataset"> case SYSROWNUM%2==0?#row0:#row1; fontsize:9px; </style>
* <style name="row0"> import(parent); bgcolor:#FFFFFF; </style> <style name="row1">
* import(parent); bgcolor:#CAEAFE; </style>
*/
public void exportToExcel(
String title, String creator, List<TableData> tableDataLst, String subTitleName)
throws Exception {
HSSFWorkbook wb = new HSSFWorkbook(); // 创建新的Excel 工作簿
HashMap<String, HSSFCellStyle> styles = initStyles(wb); // 初始化表头样式
int i = 1;
for (TableData tableData : tableDataLst) {
String sheetTitle = tableData.getSheetTitle();
sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + i : sheetTitle;
wb =
writeSheet(
wb, tableData.getSheetTitle(), styles, creator, tableData, subTitleName); // 写入工作表
i++;
}
String sFileName = title + ".xls";
response.setHeader(
"Content-Disposition",
"attachment;filename=".concat(String.valueOf(URLEncoder.encode(sFileName, "UTF-8"))));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
wb.write(response.getOutputStream());
}
public void beforeBody(TableModel model) {
logger.debug("XlsView.init()");
moneyFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
if (StringUtils.isEmpty(moneyFormat)) {
moneyFormat = DEFAULT_MONEY_FORMAT;
}
percentFormat =
model
.getPreferences()
.getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
if (StringUtils.isEmpty(percentFormat)) {
percentFormat = DEFAULT_PERCENT_FORMAT;
}
wb = new HSSFWorkbook();
sheet = wb.createSheet();
wb.setSheetName(0, "Export Workbook");
styles = initStyles(wb);
ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
createHeader(model);
}
public void addSheet(String name) {
if (name.length() > 30) {
name = name.substring(0, 25);
}
boolean found = false;
String tempName = name.replace("/", "-");
int i = 0;
while (true) {
if (i > 1000) {
break;
}
for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
if (found) {
break;
}
}
if (!found) {
break;
} else {
i++;
tempName = name + " (" + i + ")";
}
}
activeSheet = workbook.createSheet(tempName);
nextRowId = 0;
}
/**
* Reads userdata from all sheets in workbook.
*
* @return {@link List} of {@link User}.
*/
public List<User> getUsers() {
List<User> vUsers = new ArrayList<User>();
if (mFileType == FileTypes.EXCEL && mWorkbook != null) {
// go through every sheet as house
logger.info("Importing user data from excel file");
int vNumberOfSheets = mWorkbook.getNumberOfSheets();
for (int i = 0; i < vNumberOfSheets; i++) {
HSSFSheet vSheet = mWorkbook.getSheetAt(i);
String vSheetName = vSheet.getSheetName();
logger.info("Sheet name: " + vSheetName);
// try to figure out columns to use
int[] vColumns = getUserdataColumnsFromSheet(vSheet);
// get userdata from sheet
vUsers.addAll(getUsers(vSheet, vColumns));
}
} else if (mFileType == FileTypes.CSV && mExcelFile != null) {
logger.info("Importing user data from csv file.");
vUsers.addAll(getUsers(mExcelFile));
} else {
logger.error("Cannot get userdata from workbook. No workbook loaded!");
}
return vUsers;
}
/**
* POI:解析Excel文件中的数据并把每行数据封装成一个实体
*
* @param fis 文件输入流
* @return List<EmployeeInfo> Excel中数据封装实体的集合
*/
public static List<String> importControlsByPoi(InputStream fis) {
// 这里是解析出来的Excel的数据存放的List集合
List<String> controls = new ArrayList<String>();
try {
// 创建Excel工作薄
HSSFWorkbook hwb = new HSSFWorkbook(fis);
// 得到第一个工作表
HSSFSheet sheet = hwb.getSheetAt(0);
HSSFRow row = null;
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
sheet = hwb.getSheetAt(i);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
row = sheet.getRow(j);
// 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断，并做相应的处理
if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
&& !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
return controls;
}
public static String readExcel(String excelPath, int sheetNu, int rowNu, int cellNu)
throws Exception {
/** read Excel file with the Path get cell in sheetNu, rowNu and cellNu */
String cellValue = null;
FileInputStream fos = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fos);
HSSFSheet sheet = wb.getSheetAt(sheetNu);
HSSFRow row = sheet.getRow(rowNu);
try {
HSSFCell cell = row.getCell(cellNu);
if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
cellValue = cell.getStringCellValue();
// System.out.println("_________________"+cellValue);
}
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
cellValue = Long.toString((long) (cell.getNumericCellValue()));
// System.out.println("_________________"+cellValue);
}
return cellValue;
} catch (NullPointerException e) {
return null;
}
}
private void createWorkbook() {
this.workbook = new HSSFWorkbook();
this.dateCellStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
short dateFormat = format.getFormat(getDatePattern());
this.dateCellStyle.setDataFormat(dateFormat);
}
public void testRemoveNewRow_bug46312() {
// To make bug occur, rowIndex needs to be >= ValueRecordsAggregate.records.length
int rowIndex = 30;
ValueRecordsAggregate vra = new ValueRecordsAggregate();
try {
vra.removeAllCellsValuesForRow(rowIndex);
} catch (IllegalArgumentException e) {
if (e.getMessage().equals("Specified rowIndex 30 is outside the allowable range (0..30)")) {
throw new AssertionFailedError("Identified bug 46312");
}
throw e;
}
if (false) { // same bug as demonstrated through usermodel API
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(rowIndex);
if (false) { // must not add any cells to the new row if we want to see the bug
row.createCell(0); // this causes ValueRecordsAggregate.records to auto-extend
}
try {
sheet.createRow(rowIndex);
} catch (IllegalArgumentException e) {
throw new AssertionFailedError("Identified bug 46312");
}
}
}
private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
int colnum = 0;
for (Map.Entry<String, Object> component : getComponents(entity)) {
String componentName = component.getKey();
headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
PrimitiveType primitiveType;
if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
else
throw new UnsupportedOperationException(
"Can only export simple type attributes, "
+ "failed to export "
+ entity.type()
+ '.'
+ cd.getName());
Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
String formatString = null;
if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
if (formatString != null) {
HSSFDataFormat dataFormat = workbook.createDataFormat();
CellStyle columnStyle = workbook.createCellStyle();
columnStyle.setDataFormat(dataFormat.getFormat(formatString));
sheet.setDefaultColumnStyle(colnum, columnStyle);
}
colnum++;
}
}
/**
* @param SheetName excel里面的sheet的名字 （可以任意写，一般和excelname一样）
* @param response 要写的流
* @param HeadName 数据的表头 即列名
* @param data 数据 二维数组
* @return
* @throws IOException
*/
public static OutputStream creatExcel(
String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth)
throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(SheetName);
CellStyle s = new CellStyle();
Font f12 = new Font();
HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12);
// sheet.setDefaultColumnWidth(50);
sheet.setDefaultRowHeight((short) 400);
sheet.setColumnWidth(0, 18 * 256);
OutputStream os = new FileOutputStream(file);
// 设置excel的表头
for (int i = 0; i < HeadName.size(); i++) {
ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
}
// 设置excel数据
for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
}
}
// 设置excel 表格的列宽
for (int i = 0; i < HeadName.size(); i++) {
sheet.setColumnWidth(i, ColumnWidth[i]);
}
workbook.write(os);
return os;
}
private void autoSizeColumns() {
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
for (int colnum = 0; colnum <= sheet.getLastRowNum(); colnum++) sheet.autoSizeColumn(colnum);
}
}
/**
* Fills given row with headers
*
* @param wb
* @param sheet
* @param row
*/
private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) {
// create style of header font
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// here we could create array of strings, I mean headers
String[] headers = {
this.iwrb.getLocalizedString("school.child", "Child"),
this.iwrb.getLocalizedString("school.personal_id", "Personal ID"),
this.iwrb.getLocalizedString("school.e-mail", "Email"),
this.iwrb.getLocalizedString("school.address", "Address"),
this.iwrb.getLocalizedString("school.zip_code", "Zip code"),
this.iwrb.getLocalizedString("school.city", "City"),
this.iwrb.getLocalizedString("school.phone", "Phone"),
this.iwrb.getLocalizedString("school.gender", "Gender"),
this.iwrb.getLocalizedString("school.last_provider", "Last provider"),
this.iwrb.getLocalizedString("school.rejection_date", "Rejection date")
};
int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16};
HSSFCell cell;
for (int i = 0; i < headers.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256));
}
}
public void exportDataByHssf(HttpServletRequest request, HttpServletResponse response)
throws IOException, ExcelPortException, DbPropertyException, SQLException {
String header = request.getParameter("header") == null ? null : request.getParameter("header");
String where = request.getParameter("where") == null ? null : request.getParameter("where");
Date date = new Date();
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String filename = format.format(date);
HSSFWorkbook web = customerMgm.exportExcelByHeaderAndWhere(header, where);
if (web != null) {
response.setContentType("text/html");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
OutputStream out = response.getOutputStream();
web.write(out);
out.flush();
out.close();
} else {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");
out.println("excel数据导出失败！");
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}
}
@SuppressWarnings("unchecked")
@Override
protected String executeMetier() {
try {
// constitution du tableau excel
HSSFWorkbook lExcelDoc =
ExportExcel.exportPlanningMoisPilote(
(Map<Integer, Map<Integer, Map<String, String>>>)
session.get(PilotageConstants.PLANNING_LISTE_MOIS_PILOTE),
(List<Users>) session.get(PilotageConstants.PLANNING_LISTE_USER),
(Date) session.get(PilotageConstants.PLANNING_SELECT_DATE),
(List<Planning_Vacation>) session.get(PilotageConstants.PLANNING_SELECT_COULEUR));
titre = getExcelTitle();
// mise dans un flux
ByteArrayOutputStream baos = new ByteArrayOutputStream();
lExcelDoc.write(baos);
excelStream = new ByteArrayInputStream(baos.toByteArray());
return OK;
} catch (Exception e) {
error = getText("error.message.generique") + " : " + e.getMessage();
erreurLogger.error("Export Planning mensuelle des pilotes - ", e);
return ERROR;
}
}
public void remover(int ID) {
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) {
HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);
novaRow.createCell(0).setCellValue(0);
novaRow.createCell(1).setCellValue("");
novaRow.createCell(2).setCellValue("");
novaRow.createCell(3).setCellValue("");
novaRow.createCell(4).setCellValue("");
novaRow.createCell(5).setCellValue("");
novaRow.createCell(6).setCellValue("");
FileOutputStream nFile = null;
try {
nFile = new FileOutputStream("Jogos.xls");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
this.planilha.write(nFile);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
nFile.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@SuppressWarnings("unchecked")
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
resp.setContentType("application/vnd.ms-excel");
resp.setHeader("Content-Disposition", "attachment;filename=Rezultati.xls");
OutputStream out = resp.getOutputStream();
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("results");
HSSFRow headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Voting option");
headerRow.createCell(1).setCellValue("Votes received");
int t = 1;
for (VotingResultEntry vre :
(List<VotingResultEntry>) req.getSession().getAttribute("results")) {
HSSFRow row = sheet.createRow(t++);
row.createCell(0).setCellValue(vre.getName());
row.createCell(1).setCellValue(vre.getVotes());
}
hwb.write(out);
out.flush();
out.close();
}
public void testCreateCFRuleRecord() {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
CFRuleRecord record = CFRuleRecord.create(sheet, "7");
testCFRuleRecord(record);
// Serialize
byte[] serializedRecord = record.serialize();
// Strip header
byte[] recordData = new byte[serializedRecord.length - 4];
System.arraycopy(serializedRecord, 4, recordData, 0, recordData.length);
// Deserialize
record = new CFRuleRecord(TestcaseRecordInputStream.create(CFRuleRecord.sid, recordData));
// Serialize again
byte[] output = record.serialize();
// Compare
assertEquals("Output size", recordData.length + 4, output.length); // includes sid+recordlength
for (int i = 0; i < recordData.length; i++) {
assertEquals("CFRuleRecord doesn't match", recordData[i], output[i + 4]);
}
}
