package poi;
import java.io.FileOutputStream;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.Test;
/** * @Description: poi实现输出信息到excel文件 * @Author: nutony * @Date: 2013-05-15 */public class Test3SXSSF { public static void main(String[] args) throws Exception { Test3SXSSF tm = new Test3SXSSF(); tm.jdbcex(true); } @Test public void print() throws Exception{ String xlsFile = "c:/poiSXXFSBigData.xlsx"; Workbook wb = new SXSSFWorkbook(100); Sheet sheet = wb.createSheet("我的第一个工作簿"); //建立新的sheet对象
Row nRow = null; Cell nCell = null; for(int i=0;i<100;i++){ nRow = sheet.createRow(i); for(int j=0;j<20;j++){ nCell = nRow.createCell(j); nCell.setCellValue("我是单元格传智播客"); nCell.setCellStyle(style(wb)); } System.out.println(i); } FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close(); System.out.println("finish."); } @Test public void printXSSF() throws Exception{ String xlsFile = "c:/poiXSSFData.xlsx"; Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("我的第一个工作簿"); //建立新的sheet对象 Row nRow = null; Cell nCell = null; for(int i=0;i<100;i++){ nRow = sheet.createRow(i); for(int j=0;j<20;j++){ nCell = nRow.createCell(j); nCell.setCellValue("我是单元格传智播客"); nCell.setCellStyle(style(wb)); } System.out.println(i); } FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close(); System.out.println("finish."); }
@Test public void jdbcex(boolean isClose) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException, InterruptedException { String xlsFile = "c:/poiSXXFSDBBigData.xlsx"; //输出文件 Workbook wb = new SXSSFWorkbook(100); //创建excel文件，内存只有100条记录【关键语句】 Sheet sheet = wb.createSheet("我的第一个工作簿"); //建立新的sheet对象
Row nRow = null; Cell nCell = null;
//使用jdbc链接数据库 Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost:3306/jkmore100?characterEncoding=UTF-8"; String user = "root"; String password = "root"; Connection conn = DriverManager.getConnection(url, user,password); Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql = "select * from hpa limit 100000"; //100万测试数据 ResultSet rs = stmt.executeQuery(sql); //bug 要分次读取，否则记录过多 long startTime = System.currentTimeMillis(); //开始时间 System.out.println("strat execute time: " + startTime); //context int rowNo = 0; int colNo = 0; while(rs.next()) { colNo = 0; nRow = sheet.createRow(rowNo++);
nCell = nRow.createCell(colNo++); nCell.setCellValue(rs.getString(colNo)); nCell = nRow.createCell(colNo++); nCell.setCellValue(rs.getString(colNo)); if(rowNo0==0){ System.out.println("row no: " + rowNo); } Thread.sleep(1); //休息一下，防止对CPU占用 } long finishedTime = System.currentTimeMillis(); //处理完成时间 System.out.println("finished execute time: " + (finishedTime - startTime)/1000 + "m"); FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close(); long stopTime = System.currentTimeMillis(); //写文件时间 System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m"); if(isClose){ this.close(rs, stmt, conn); } } //close resource private void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException{ rs.close(); stmt.close(); conn.close(); } //设置单元格样式 private CellStyle style(Workbook wb){ CellStyle curStyle = wb.createCellStyle(); Font curFont = wb.createFont(); //设置字体 curFont.setFontName("微软雅黑"); //设置英文字体 curFont.setCharSet(XSSFFont.DEFAULT_CHARSET); //设置中文字体，那必须还要再对单元格进行编码设置 curFont.setFontHeightInPoints((short)10); //字体大小 curStyle.setFont(curFont); curStyle.setBorderTop(XSSFCellStyle.BORDER_THICK); //粗实线 curStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //实线 curStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); //比较粗实线 curStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); //实线 curStyle.setWrapText(true); //换行 curStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); //横向具右对齐 curStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); //单元格垂直居中 return curStyle; }}
转载于:https://www.cnblogs.com/songyunxinQQ529616136/p/6551634.html
