本次使用到的jar包
1、从数据库读取数据写出到Excel
public class CreateExcel01 { // 数据库查询 public static List<Account> query() { String sql = "select * from tb_account"; List<Account> list = BaseDao.findRows(sql, null, Account.class); return list; } // 创建Excel public static void createExcel(){ try { // 获取桌面路径 FileSystemView fsv = FileSystemView.getFileSystemView(); String desktop = fsv.getHomeDirectory().getPath(); String filePath = desktop + "/account.xls"; File file = new File(filePath); OutputStream outputStream = new FileOutputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet = workbook.createSheet("Sheet1"); // 创建首行/头（第0行开始） HSSFRow head = sheet.createRow(0); String[] header = new String[]{"账户id","账户名称","账户类型","账户金额","账户备注","创建时间","用户id","更新时间"}; for (int i=0;i<header.length;i++){ // 设置首行信息 head.createCell(i).setCellValue(header[i]); } head.setHeightInPoints(20); // 设置行的高度 // 从数据查询返回的集合 List<Account> accounts=query(); // 日期格式化 HSSFCellStyle cellStyle2 = workbook.createCellStyle(); HSSFCreationHelper creationHelper = workbook.getCreationHelper(); // 设置日期格式 cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(5, 20 * 256); sheet.setColumnWidth(7, 20 * 256);// 设置列的宽度 // 保留两位小数 HSSFCellStyle cellStyle3 = workbook.createCellStyle(); cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); for(int i=0;i<accounts.size();i++) { // 创建行（从第一行开始） HSSFRow row1 = sheet.createRow(i + 1); // id row1.createCell(0).setCellValue(accounts.get(i).getId()); // 账户名称 row1.createCell(1).setCellValue(accounts.get(i).getAccountName()); // 账户类型 row1.createCell(2).setCellValue(accounts.get(i).getAccountType()); // 账户金额（保留两位小数） HSSFCell money = row1.createCell(3); money.setCellStyle(cellStyle3); money.setCellValue(accounts.get(i).getMoney()); // 账户备注 row1.createCell(4).setCellValue(accounts.get(i).getRemark()); // 创建时间（格式化时间） HSSFCell date1 = row1.createCell(5); date1.setCellStyle(cellStyle2); date1.setCellValue(accounts.get(i).getCreateTime()); // 用户id row1.createCell(6).setCellValue(accounts.get(i).getUid()); // 更新时间 HSSFCell date2 = row1.createCell(7); date2.setCellStyle(cellStyle2); date2.setCellValue(accounts.get(i).getUpdateTime()); } workbook.setActiveSheet(0); workbook.write(outputStream); outputStream.close(); } catch (IOException e1) { e1.printStackTrace(); } } }
2、从Excel读取写入到数据库
package com.shsxt; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.functions.Rows; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; public class ReadExcelToDB { public List<List<Object>> importExcel(File file) { List<List<Object>> dataList = null; try { dataList = new ArrayList<List<Object>>(); // Excel对象 Workbook workbook = null; // 获取文件名 String fileName = file.getName().toLowerCase(); // 判断后缀 if (fileName.endsWith("xls")) { workbook = new HSSFWorkbook(new FileInputStream(file)); } else if (fileName.endsWith("xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(file)); } else { throw new RuntimeException("该文件不是Excel文件"); } // 获取Excel中的第一个表格 Sheet sheet = workbook.getSheet("Sheet1"); // 得到表格中的数据的行数，最后一行 int rows = sheet.getLastRowNum(); if (rows == 0) { throw new RuntimeException("表格中没有数据"); } Row row = null; //行对象 Iterator<Cell> cols = null; // 列对象的迭代器 List<Object> list = null; for (int i = 1; i <= rows; i++) { row = sheet.getRow(i); // 获取第i行 if (row != null) { cols = row.cellIterator(); // 获取该行的迭代器 list = new ArrayList<Object>(); while (cols.hasNext()) { // 循环获取每一列数据存到list中 list.add(getCellObj(cols.next())); } // 将这行数据存到集合dataList中 dataList.add(list); } } } catch (IOException e) { e.printStackTrace(); } return dataList; } private Object getCellObj(Cell cell) { if (cell == null) { return ""; } Object cellObj; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: //字符串 cellObj = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: //数字 // poi把日期类型也归为数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)){ // 获取日期对象并格式化 Date date=cell.getDateCellValue(); cellObj= DateFormatUtils.format(date,"yyyy-MM-dd"); } else{ /*CellStyle cellStyle=cell.getCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));*/ cellObj = cell.getNumericCellValue(); } break; case HSSFCell.CELL_TYPE_BOOLEAN: //boolean cellObj = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: //公式 cellObj = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BLANK: //空 cellObj = ""; break; case HSSFCell.CELL_TYPE_ERROR: //错误 cellObj = ""; break; default: cellObj=""; break; } return cellObj; } } class Test { public static void main(String[] args) { File file = new File("C:\Users\Administrator\Desktop\no1.xls"); ReadExcelToDB readExcelToDB = new ReadExcelToDB(); List<List<Object>> lists=readExcelToDB.importExcel(file); for (List<Object> ObjList:lists){ for (Object object:ObjList){ System.out.println(object); }
System.out.println("__________"); } } }
读取的结果是：
调用数据库插入就可以往插入到数据库了