62,616
社区成员
发帖
与我相关
我的任务
分享
/******************************************************POI处理数据导出***************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(10000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourcefile=System.getenv("temp")+"\\oqcRdmInsp.xls";
ExcelWriter excelWriter = new ExcelWriter(new FileOutputStream(sourcefile));
for(int currentPage = 1 ; currentPage <= 1 ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,excelWriter);
}
try{
excelWriter.export();
System.out.println(" 导出Excel文件[成功]");
} catch (IOException ex) {
System.out.println(" 导出Excel文件[失败]");
ex.printStackTrace();
}
//写入数据并关闭文件
pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourcefile));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null; //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常!" + e.getMessage());
return null;
}
}
/*****************************************************POI处理数据导出 END*******************************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,ExcelWriter excelWriter) {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
return null;
}
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
System.out.println(columnCount);
int rowNum = 0;
excelWriter.createRow(rowNum++);
excelWriter.setCell(3,"OQC抽检");
excelWriter.createRow(rowNum++);
for(int i = 1; i <= columnCount; i++) {
excelWriter.setCell(i-1,md.getColumnName(i).toString());
}
while(rs.next()){
excelWriter.createRow(rowNum++);
for (int i = 1; i <= columnCount; i++){
Object o = rs.getObject(i);
if(columnCount == 2 && o!= null){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date datetime = sdf1.parse((String)o);
String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
o = weekdate;
}
excelWriter.setCell(i-1,o==null?"":o.toString());
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
public class ExcelWriter {
// 定制浮点数格式
private static String NUMBER_FORMAT = "#,##0.00";
// 定制日期格式
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
private OutputStream out = null;
private Workbook workbook = null;
private Sheet sheet = null;
private Row row = null;
public ExcelWriter() {
}
public ExcelWriter(OutputStream out) {
this.out = out;
this.workbook = new SXSSFWorkbook(128);;
this.sheet = workbook.createSheet();
}
/**
* 导出Excel文件
* @throws IOException
*/
public void export() throws FileNotFoundException, IOException {
try {
workbook.write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
throw new IOException(" 生成导出Excel文件出错! ", e);
} catch (IOException e) {
throw new IOException(" 写入Excel文件出错! ", e);
}
}
/**
* 增加一行
* @param index 行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
}
/**
* 获取单元格的值
* @param index 列号
*/
public String getCell(int index){
Cell cell = this.row.getCell((short) index);
String strExcelCell = "";
if (cell != null) { // add this condition
// judge
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case Cell.CELL_TYPE_NUMERIC: {
strExcelCell = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
strExcelCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
return strExcelCell;
}
/**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, int value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
}
/**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, double value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));//设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}
/**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index, String value) {
Cell cell = this.row.createCell((short) index);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(value);
}
/**
* 设置单元格
*
* @param index 列号
* @param value 单元格填充值
*/
public void setCell(int index,Calendar value) {
Cell cell = this.row.createCell((short) index);
cell.setCellValue(value.getTime());
CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
}
}
/**
* The maximum number of rows excel allows in a worksheet
*/
private final static int numRowsPerSheet = 65536;
WorkbookSettings wbs = new WorkbookSettings();
wbs.setUseTemporaryFileDuringWrite(true); //设置使用中间文件,而不是全内存保持输出内容。
book = Workbook.createWorkbook(new File(newFileName), wbs); // 把WorkbookSettings对象设到Workbook里。
package sh.pl;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class TestExcel {
public static void main(String[] args) {
String newFileName = "d:\\test_performance.xls";
int rows = 60000;
int cols = 12;
Date time = new Date();
WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(new File(newFileName));
WritableSheet sheet = book.createSheet("OQC抽检", 0);
exportToExcel(book, sheet, rows, cols);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (book != null) {
try {
book.write();
book.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println(new Date().getTime() - time.getTime());
}
public static void exportToExcel(WritableWorkbook book, WritableSheet sheet, int row, int col) throws RowsExceededException, WriteException {
for (int i = 0; i < row; i++){
for (int j = 0; j < col; j++) {
String value = i + "_" + j;
if (j == 2){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
value = sdf1.format(new Date());
}
sheet.addCell(new Label(j, i, value));
}
}
}
}
public String export() throws Exception {
/******************************合并多个EXCEL方式**************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(3000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourceRoot=System.getenv("temp")+"\\oqcRdmInsp";
Integer row = 1;
List<String> sourcefiles = new ArrayList<String>();
for(int currentPage = 1 ; currentPage <= 4 ; currentPage++){
String newFileName = sourceRoot + currentPage + ".xls";
sourcefiles.add(newFileName);
WritableWorkbook book = Workbook.createWorkbook(new File(newFileName));
WritableSheet sheet = book.createSheet("OQC抽检", 0);
WritableCellFormat wcf = new jxl.write.WritableCellFormat();
Label wlabel = new Label(3,0,"OQC抽检表",wcf);
sheet.addCell(wlabel);
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,book,sheet,row);
book.write();
book.close();
}
String destFile = sourceRoot + ".xls";
ExcelMergeUtil.merge(sourcefiles, destFile);
//写入数据并关闭文件
pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourceRoot+".xls"));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null; //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常!" + e.getMessage());
return null;
}
}
/************************合并多个EXCEL方式 END****************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,WritableWorkbook book, WritableSheet sheet,int row) {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
return null;
}
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
if(row <=1){
for(int i = 1; i <= columnCount; i++) {
sheet.addCell(new Label(i, 1 ,md.getColumnName(i)));
}
}
while(rs.next()){
row++;
for (int i = 1; i <= columnCount; i++){
Object o = rs.getObject(i);
if(columnCount == 2 && o!= null){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date datetime = sdf1.parse((String)o);
String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
o = weekdate;
}
sheet.addCell(new Label(i, row,(String)(o!=null?o.toString():o)));
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return row;
}
}
package sh.pl;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.poi.ss.usermodel.Cell;
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;
public class TestExcelPOI {
public static void main(String[] args) {
String newFileName = "d:\\test_performance.xlsx";
int rows = 60000;
int cols = 70;
Date time = new Date();
Workbook book = null;
Sheet sheet = null;
BufferedOutputStream out = null;
try {
book = new SXSSFWorkbook(128); //缓存128在内存。
sheet = book.createSheet("OQC抽检");
exportToExcel(book, sheet, rows, cols);
out = new BufferedOutputStream(new FileOutputStream(newFileName));
book.write(out);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println(new Date().getTime() - time.getTime());
}
public static void exportToExcel(Workbook book, Sheet sheet, int row, int col) throws RowsExceededException, WriteException, IOException {
for (int i = 0; i < row; i++){
Row sheetRow = sheet.createRow(i);
for (int j = 0; j < col; j++) {
Cell cell = sheetRow.createCell(j);
String value = i + "_" + j;
if (j == 2){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
value = sdf1.format(new Date());
}
cell.setCellValue(value);
}
}
}
}
/***********************************HTML方式导出数据 ************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(20000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():(records/pageBean.getPageSize()+1);
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
for(int currentPage = 1 ; currentPage <= pages ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(response,findBean,pageBean);
}
//写入数据并关闭文件
pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
return null;
}
/**********************************HTML方式导出数据 END********************************************/
@Override
public void exportToExcel(HttpServletResponse response, OqcInfoBean findBean, PageBean pageBean) throws IOException {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
OutputStream os= response.getOutputStream();
BufferedWriter bw = new BufferedWriter(new PrintWriter(new OutputStreamWriter(os)));
bw.write("OQC抽检表 <br>");
if (rs != null) {
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
bw.write("<table cellspacing=\"0\" cellpadding=\"2\" border=\"1\"><tr>");
for(int i = 1; i <= columnCount; i++){
bw.write("<th>"+ md.getColumnName(i)+" </th>");
}
bw.write("</tr>");
while(rs.next()){
bw.write("<tr>");
for (int i = 1; i <= columnCount; i++){
Object o = rs.getObject(i);
if(columnCount == 0||columnCount == 3||columnCount == 5||columnCount == 7){
bw.write("<td nowrap>" + (String)(o!=null?("'"+o.toString()):"") +" </td>");
continue;
}
if(columnCount == 2 && o!= null){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date datetime = sdf1.parse((String)o);
String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
o = weekdate;
}
bw.write("<td nowrap>" + (String)(o!=null?(o.toString()):"") +" </td>");
}
bw.write("</tr>");
}
bw.write("</table>");
bw.flush();
bw.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/******************************************************POI处理数据导出***************************************************/
this.getHttpServletRequest().getSession().setAttribute("pageBean", pageBean);
pageBean.setPageSize(4000);
int records = oqcInfoService.getAllRows(findBean);
int pages = records%pageBean.getPageSize()==0?records/pageBean.getPageSize():records/pageBean.getPageSize()+1;
String sourcefile=System.getenv("temp")+"\\oqcRdmInsp.xls";
ExcelWriter excelWriter = new ExcelWriter(new FileOutputStream(sourcefile));
for(int currentPage = 1 ; currentPage <= 1 ; currentPage++){
pageBean.setCurrentPage(currentPage);
oqcInfoService.exportToExcel(pageBean,findBean,excelWriter);
}
try{
excelWriter.export();
System.out.println(" 导出Excel文件[成功]");
} catch (IOException ex) {
System.out.println(" 导出Excel文件[失败]");
ex.printStackTrace();
}
//写入数据并关闭文件
pageBean = (PageBean)this.getHttpServletRequest().getSession().getAttribute("pageBean");
try {
HttpServletResponse request = ServletActionContext.getResponse();
request.setContentType("application/vnd.ms-excel");
request.setHeader("Content-Disposition","attachment; filename=oqcRdmInsp.xls");
InputStream is = new FileInputStream(new File(sourcefile));
byte[] b = new byte[4096];
int len;
while ((len = is.read(b)) > 0){
request.getOutputStream().write(b, 0, len);
}
is.close();
return null; //必须返回null 否则会报错.
} catch (Exception e) {
this.clearMessages();
this.addActionMessage("导出出现异常!" + e.getMessage());
return null;
}
}
/*****************************************************POI处理数据导出 END*******************************************************/
@SuppressWarnings("deprecation")
@Override
public Integer exportToExcel(PageBean pageBean, OqcInfoBean findBean,ExcelWriter excelWriter) {
Connection conn = null;
ResultSet rs = null;
Session sess=null;
Statement stmt = null;
String sql = null;
try {
sess=oqcInfoDao.getsession();
conn=sess.connection();
stmt = conn.createStatement();
if(findBean == null){
sql = createSQL1(pageBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
}
else{
int start = 0;
int end = 0;
if(pageBean.getCurrentPage()==0 || pageBean.getCurrentPage() == 1){
start = 1;
end = pageBean.getPageSize();
}
else{
start = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1;
end = start + pageBean.getPageSize() -1;
}
sql = createSQL(findBean);
sql = "select a.*,rownum as rn from (" + sql + ") a";
sql = "select * from (" +sql +") t where t.rn between "+ start + " and " + end;
}
rs = (ResultSet) stmt.executeQuery(sql);
if (rs == null) {
return null;
}
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
System.out.println(columnCount);
int rowNum = 0;
excelWriter.createRow(rowNum++);
excelWriter.setCell(3,"OQC抽检");
excelWriter.createRow(rowNum++);
for(int i = 1; i <= columnCount; i++) {
excelWriter.setCell(i-1,md.getColumnName(i).toString());
}
while(rs.next()){
excelWriter.createRow(rowNum++);
for (int i = 1; i <= columnCount; i++){
Object o = rs.getObject(i);
if(columnCount == 2 && o!= null){
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
Date datetime = sdf1.parse((String)o);
String weekdate = String.valueOf(WeekHandle.getWeekFromDate(datetime));//计算周次
o = weekdate;
}
excelWriter.setCell(i-1,(String)(o==null?"":o.toString()));
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (ParseException e) {
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
sess.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/**
* The maximum amount of bytes available for the SST record
*/
private static int maxBytes = 8228 - // max length
4; // standard biff record stuff