利用poi操作excel 备忘如下
写操作:
读操作:
写操作:
FileOutputStream fos = new FileOutputStream("d:/stock.xls");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "stock");
HSSFCell cell;
int i = 0;
HSSFCellStyle numberCellStyle = wb.createCellStyle();
HSSFDataFormat numberFormat = wb.createDataFormat();
numberCellStyle.setDataFormat(numberFormat.getFormat("0.00"));
HSSFCellStyle profitCellStyle = wb.createCellStyle();
HSSFDataFormat profitFormat = wb.createDataFormat();
profitCellStyle.setDataFormat(profitFormat.getFormat("0.00%"));
for (Iterator it = data1.iterator(); it.hasNext();) {
Map m = (Map) it.next();
HSSFRow row = s.createRow(i++);
String name = (String) m.get("NAME");
cell = row.createCell(0);
cell.setCellValue(name);
String code = (String) m.get("CODE");
cell = row.createCell(1);
cell.setCellValue(code);
BigDecimal importPrice = (BigDecimal) m.get("IMPORT_PRICE");
cell = row.createCell(2);
cell.setCellStyle(numberCellStyle);
cell.setCellValue(importPrice.doubleValue());
BigDecimal closePrice = (BigDecimal) m.get("CLOSE_PRICE");
cell = row.createCell(3);
cell.setCellStyle(numberCellStyle);
cell.setCellValue(closePrice.setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue());
String profit = (String) m.get("PROFIT");
cell = row.createCell(4);
cell.setCellValue(profit);
cell = row.createCell(5);
cell.setCellFormula(getProfitFomulaByRowNum(row.getRowNum()));
cell.setCellStyle(profitCellStyle);
}
wb.write(fos);
fos.close();
读操作:
FileInputStream fis = new FileInputStream(
"D:/eclipse/workspaces/handworkspace/xyzq/xyzq-data/data/stockpool.xls"); // 根据excel文件路径创建文件流
POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流
HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取excel工作簿
HSSFSheet sheet = wb.getSheetAt(0); // 读取excel的sheet,0表示读取第一个、1表示第二个.....
Map cateMap = cateMap();
// 获取sheet中总共有多少行数据sheet.getPhysicalNumberOfRows()
// log.info("sheet.getPhysicalNumberOfRows()="+sheet.getPhysicalNumberOfRows());
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
HSSFRow row = sheet.getRow(i); // 取出sheet中的某一行数据
if (row != null) {
StringBuffer sb = new StringBuffer();
sb.append(row.getCell(0).getStringCellValue()).append(" ")
.append(row.getCell(1).getStringCellValue())
.append(" ").append(
row.getCell(2).getDateCellValue()).append(
" ").append(
row.getCell(3).getNumericCellValue())
.append(" ").append(
row.getCell(4).getStringCellValue())
.append(" ").append(
row.getCell(5).getStringCellValue())
.append(" ");
System.out.println(sb);
Map param = new HashMap();
param.put("CODE", row.getCell(0).getStringCellValue());
param.put("NAME", row.getCell(1).getStringCellValue());
param.put("IMPORT_TIME", row.getCell(2).getDateCellValue());
param.put("IMPORT_PRICE", new BigDecimal(row.getCell(3)
.getNumericCellValue()).setScale(2,
BigDecimal.ROUND_HALF_UP));
param.put("DESCRIPTION", row.getCell(5)
.getStringCellValue());
param.put("RISK_PROMPT", row.getCell(6)
.getStringCellValue());
param.put("POOL", new Long(STOCK_POOL_TYPE_0));
param.put("CATE_ID", cateMap.get(row.getCell(4)
.getStringCellValue()));
this.insert("TG_STOCK_POOL", param);
}
}
} catch (Exception e) {
e.printStackTrace();
}