public static void main(String[] args) {
//创建excel表格
HSSFWorkbook wb=new HSSFWorkbook()
//创建工作表,并指定名称
HSSFSheet sheet = wb.createSheet("工作表的名字")
//创建工作表,并指定名称
HSSFSheet sheet2 = wb.createSheet("工作表2")
//获取所有的工作表
Iterator<Sheet> sheetIterator = wb.sheetIterator()
//获取指定名字的工作表
HSSFSheet sheet3 = wb.getSheet("工作表2")
//设置第3列的宽度
sheet.setColumnWidth(3, 10*256)
//设置每一列的默认宽度
sheet.setDefaultColumnWidth(10*256)
//设置行默认的行高
sheet.setDefaultRowHeight((short)300)
//设置样式
HSSFCellStyle cellStyle = wb.createCellStyle()
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
//设置标题字体格式
HSSFFont font = wb.createFont()
//设置字体样式
font.setFontHeightInPoints((short)20)
font.setFontName("Courier New")
font.setItalic(true)
cellStyle.setFont(font)
//style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex())
//style1.setFillPattern(CellStyle.SOLID_FOREGROUND)
cellStyle.setWrapText(true)
cellStyle.setBorderBottom((short)1)
cellStyle.setBorderLeft((short)1)
cellStyle.setBorderRight((short)1)
cellStyle.setBorderTop((short)1)
HSSFDataFormat format= wb.createDataFormat()
cellStyle.setDataFormat(format.getFormat("¥#,##0"))
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"))
List<String> th=new ArrayList<>()
th.add("序列")
th.add("姓名")
th.add("性别")
th.add("年龄")
List<List<String>> tr=new ArrayList<List<String>>()
for(int i=0
List<String> td=new ArrayList<>()
td.add(String.valueOf(i))
td.add(String.valueOf("姓名"+i))
td.add(String.valueOf("性别"+i))
td.add(String.valueOf("年龄"+i))
tr.add(td)
}
//创建第一行(即标题)
HSSFRow row = sheet.createRow(0)
HSSFCell titleCell = row.createCell(0)
titleCell.setCellType(HSSFCell.CELL_TYPE_STRING)
titleCell.setCellValue("标题名")
titleCell.setCellStyle(cellStyle)
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, th.size()))
//创建第一行(即标题)
HSSFRow sheet2Row = sheet2.createRow(0)
HSSFCell titleCell2 = sheet2Row.createCell(0)
titleCell2.setCellType(HSSFCell.CELL_TYPE_STRING)
titleCell2.setCellValue("标题名")
titleCell2.setCellStyle(cellStyle)
sheet2.addMergedRegion(new CellRangeAddress(0, 1, 0, th.size()))
HSSFRow sheet1Row2 = sheet.createRow(2)
HSSFRow sheet2Row2 = sheet2.createRow(2)
for(int i=0
HSSFCell heet1RowCell = sheet1Row2.createCell(i)
heet1RowCell.setCellValue(th.get(i))
HSSFCell heet2RowCell = sheet2Row2.createCell(i)
heet2RowCell.setCellValue(th.get(i))
}
for(int i=0
HSSFRow createRow3 = sheet.createRow(i+3)
List<String> list = tr.get(i)
for(int j=0
HSSFCell cell = createRow3.createCell(j)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(list.get(j))
}
}
for(int i=0
HSSFRow createRow3 = sheet2.createRow(i+3)
List<String> list = tr.get(i)
for(int j=0
HSSFCell cell = createRow3.createCell(j)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(list.get(j))
}
}
try {
FileOutputStream file=new FileOutputStream("e:\\workbook.xls")
wb.write(file)
file.flush()
//wb.write(fos)
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace()
}
}
private HashMap<String, Integer> parseExcel(InputStream inputStream) {
HashMap<String, Integer> map = new HashMap<String, Integer>()
try {
try (NPOIFSFileSystem fs = new NPOIFSFileSystem(inputStream)
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true)) {
Sheet sheet = wb.getSheetAt(0)
Objects.requireNonNull(sheet, "无法读取到文件中的Sheet")
int rows = sheet.getLastRowNum()
LOG.debug("上传总的行数:{}", rows)
for (int rowIndex = 1
Row row = sheet.getRow(rowIndex)
Cell codeCell = row.getCell(0)
String code = codeCell.getCellType() == Cell.CELL_TYPE_NUMERIC
? String.valueOf(new Double(codeCell.getNumericCellValue()).intValue())
: codeCell.getStringCellValue()
Objects.requireNonNull(code, "导入商品的编码不能为空")
Cell quantityCell = row.getCell(1)
switch (quantityCell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
Double quantity = quantityCell.getNumericCellValue()
Objects.requireNonNull(quantity, "商品编码:" + code + "数量不能为空")
map.put(code, quantity.intValue())
break
}
case Cell.CELL_TYPE_STRING: {
String quantity = quantityCell.getStringCellValue()
Objects.requireNonNull(quantity, "商品编码:" + code + "数量不能为空")
map.put(code, Integer.parseInt(quantity))
break
}
default:{
LOG.error("导入的数量类型单元格格式不匹配")
throw new ApplicationException("导入的数量类型单元格格式不匹配")
}
}
}
}
} catch (Exception exp) {
LOG.error("Excel文件解析发生错误", exp)
throw new ApplicationException(exp.getMessage(), exp)
}
return map
}