1,下载所需jar包地址xlsx依赖jar
2,读取表格数据代码实例
static void jx(File file) throws IOException {
InputStream is = new FileInputStream(file);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
int rowstart = xssfSheet.getFirstRowNum();
int rowEnd = xssfSheet.getLastRowNum();
for (int i = rowstart; i <= rowEnd; i++) {
XSSFRow row = xssfSheet.getRow(i);
if (null == row)
continue;
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
for (int k = cellStart; k <= cellEnd; k++) {
XSSFCell cell = row.getCell(k);
if (null == cell) {
System.out.print(k+"cell null");
continue;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue() + "\t");
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue() + "\t");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue() + "\t");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + "\t");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" 控制");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" 故障");
break;
default:
System.out.print("未知类型 ");
break;
}
}
System.out.print(cellEnd +"... \n");
}
3,写入表格代码实例
1)创建实体类
public class Export implements Serializable{
String sjbm;
double num;
public String getSjbm() {
return sjbm;
}
public void setSjbm(String sjbm) {
this.sjbm = sjbm;
}
public double getNum() {
return num;
}
public void setNum(double num) {
this.num = num;
}
}
2)写入xlsx表格实例代码,以实体类为数据载体
public void writeXls(List<Export> exportList, File file) throws Exception {
String[] options = { "条码", "批次号", "数量" };
XSSFWorkbook book = new XSSFWorkbook();
CreationHelper createHelper = book.getCreationHelper();
XSSFCellStyle style = book.createCellStyle();
XSSFCellStyle dateStyle = book.createCellStyle();
XSSFDataFormat format = book.createDataFormat();
style.setWrapText(true);
dateStyle.setWrapText(true);
XSSFSheet sheet = book.createSheet("sheet");
sheet.setColumnWidth(3, 13000);
sheet.setDefaultColumnWidth(20);
XSSFRow firstRow = sheet.createRow(0);
XSSFCell[] firstCells = new XSSFCell[3];
CellStyle styleBlue = book.createCellStyle(); // 样式对象
// 设置单元格的背景颜色为淡蓝色
styleBlue.setFillBackgroundColor(HSSFColor.GREEN.index);
styleBlue.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
styleBlue.setAlignment(CellStyle.ALIGN_CENTER);// 水平
styleBlue.setWrapText(true);// 指定当单元格内容显示不下时自动换行
Font font = book.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 280);
style.setFont(font);
dateStyle.setFont(font);
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
styleBlue.setFont(font);
for (int j = 0; j < options.length; j++) {
firstCells[j] = firstRow.createCell(j);
firstCells[j].setCellStyle(styleBlue);
firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
}
getExport(sheet, style, createHelper, exportList, dateStyle);
if (file.exists()) {
file.delete();
}
file.createNewFile();
OutputStream os = new FileOutputStream(file);
book.write(os);
os.close();
}
public void getExport(XSSFSheet sheet, XSSFCellStyle style, CreationHelper createHelper, List<Export> exportList,
XSSFCellStyle dateStyle) {
for (int i = 0; i < exportList.size(); i++) {
XSSFRow row = sheet.createRow(i + 1);
Export export = exportList.get(i);
XSSFCell hotelId = row.createCell(0);
hotelId.setCellStyle(style);
XSSFCell hotelName = row.createCell(1);
hotelName.setCellStyle(dateStyle);
XSSFCell chargeCount = row.createCell(2);
chargeCount.setCellStyle(style);
hotelId.setCellValue(export.getSjbm());
hotelName.setCellValue(date);
chargeCount.setCellValue(export.getNum());
// ta.append("写入excel开始,行数是" + (i + 1) + "\n");
}
}
这样就会写入完成入下图的表格