将数据库中的数据导出为excel文件,供其他人查看
public class POITest { public static void main(String[] args) {
POITest test = new POITest(); // test.readExcelToDB(); test.writeExcelFromDB(); } static class Book{
public String title;
public String author;
public String date;
} SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
ComboPooledDataSource dataSource;
public POITest() {
//初始化数据库连接池
try {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?user=root&password=123456"
+ "&characterEncoding=utf8&serverTimezone=UTC");
} catch (PropertyVetoException e) {
e.printStackTrace();
}
} //从数据库读取数据并保存为excel
public void writeExcelFromDB(){
List<Book> books = new ArrayList<POITest.Book>();
try {
Connection conn = dataSource.getConnection();
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select * from book");
while(resultSet.next()){
Book book = new Book();
//第一列是id
book.title = resultSet.getString(2);
book.author = resultSet.getString(3);
book.date = format.format(resultSet.getDate(4));
books.add(book);
} writeExcel(books);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} } //HSSF 写excel
private void writeExcel(List<Book> books) throws IOException{
HSSFWorkbook workbook = new HSSFWorkbook();
//创建表
HSSFSheet sheet = workbook.createSheet("书本");
//创建首行
HSSFRow topRow = sheet.createRow(0);
//创建首行单元格样式
HSSFCellStyle topCellStyle = workbook.createCellStyle();
topCellStyle.setAlignment(HorizontalAlignment.CENTER);
topCellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
topCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
topCellStyle.setBorderBottom(BorderStyle.THIN);
topCellStyle.setBorderLeft(BorderStyle.THIN);
topCellStyle.setBorderTop(BorderStyle.THIN);
topCellStyle.setBorderRight(BorderStyle.THIN);
HSSFFont topFont = workbook.createFont();
topFont.setColor(HSSFColor.BLACK.index);
topCellStyle.setFont(topFont); HSSFCell topCell = topRow.createCell(0);
topCell.setCellValue("书名");
topCell.setCellStyle(topCellStyle);
topCell = topRow.createCell(1);
topCell.setCellValue("作者");
topCell.setCellStyle(topCellStyle);
topCell = topRow.createCell(2);
topCell.setCellValue("出版日期");
topCell.setCellStyle(topCellStyle); //设置普通行单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.cloneStyleFrom(topCellStyle);
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
cellStyle.setFont(font); for (int i=0; i<books.size(); i++) {
HSSFRow row = sheet.createRow(i+1);
Book book = books.get(i);
HSSFCell cell = row.createCell(0);
cell.setCellValue(book.title);
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue(book.author);
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(book.date);
cell.setCellStyle(cellStyle);
} FileOutputStream os = new FileOutputStream("d:/book.xls");
workbook.write(os);
os.flush();
os.close();
} //从本地读取excel数据插入数据库
public void readExcelToDB(){
try {
List<Book> books = readExcel();
Connection conn = dataSource.getConnection();
String sql = "insert into book(title,author,submission_date) values(?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for (Book book : books) {
preparedStatement.setString(1, book.title);
preparedStatement.setString(2, book.author);
preparedStatement.setDate(3, new Date(format.parse(book.date).getTime()));
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} } //HSSF 读excel
private List<Book> readExcel() throws IOException{
List<Book> books = new ArrayList<POITest.Book>();
InputStream is = new FileInputStream(new File("d:/book_2.xls"));
//得到工作薄
HSSFWorkbook workbook = new HSSFWorkbook(is);
//得到工作表
Sheet sheet = workbook.getSheetAt(0);
//得到行数
int rowNum = sheet.getLastRowNum();
//首行是标题行
for(int i=1; i<=rowNum; i++){
Book book = new Book();
Row row = sheet.getRow(i);
Cell cell = row.getCell(0);
book.title = cell.getStringCellValue();
cell = row.getCell(1);
book.author = cell.getStringCellValue();
cell = row.getCell(2);
book.date = format.format(cell.getDateCellValue());
books.add(book);
} return books;
} }
book建表语句
CREATE TABLE `book` (
`id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL,
`author` varchar(40) DEFAULT NULL,
`submission_date` date DEFAULT NULL
)
设置列宽(index表示第几列,从0开始)
sheet.setColumnWidth(index, 30*256);
设置表格内容自动换行
CellStyle wrapStyle = workBook.createCellStyle();
wrapStyle.setWrapText(true); wrapCell.setCellStyle(wrapStyle);
wrapCell.setCellValue("第一行\r\n第二行");