2000年3月27日星期一

Javaで帐票下载（Web）

説明：必要なライブラリ
①poi-2.5.1-final-20040804.jar
②poi-contrib-2.5.1-final-20040804.jar
③poi-scratchpad-2.5.1-final-20040804.jar





一：服务器端生成Excel文件后在客户端执行帐票下载操作

package excelDownLoad;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpServlet;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import veriserve.common.F30Excel;

public class ExcelDownLoadServlet extends HttpServlet{
private static final long serialVersionUID = 1L;
private short bgColorNull = HSSFColor.WHITE.index;
private short bgColor = HSSFColor.GREY_25_PERCENT.index;

public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
//生成帐票
outPutXls();

//下载帐票
downloadFile(request, response);
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
doPost(request,response);
}

//生成帐票
private void outPutXls(){
try {
F30Excel xlsInstance = F30Excel.getInstance();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("人事");
sheet.setDefaultColumnWidth((short) (10.82));// デフォルトの列幅の設定

//セル幅
// sheet.setColumnWidth((short)0,(short)5120);//140ピクセル

HSSFRow row = sheet.createRow((short) 0);

//タイトル
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 9));
xlsInstance.createCell(wb, row, (short) 0, "人事情報管理システム", bgColorNull, (short)19,false,"CENTER",false);

//日付
sheet.addMergedRegion(new Region(0, (short) 10, 0, (short) 11));
xlsInstance.createCell(wb, row, (short) 10, "帳票出力：2010/09/24 14:57", bgColorNull, (short)10,false,"RIGHT",false);

HSSFRow row2 = sheet.createRow((short) 2);

xlsInstance.createCell(wb, row2, (short) 0, "検索入社理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 1, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 2, "検索退職理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 3, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 4, "検索転籍理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 5, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 6, "検索出向理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 7, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 8, "検索異動理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 9, "", bgColorNull, (short)10,true,"",false);

HSSFRow row3 = sheet.createRow((short) 4);
sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) 1));
xlsInstance.createCell(wb, row3, (short) 0, "人事情報照会一覧", bgColorNull, (short)10.82,false,"",true);

HSSFRow row4 = sheet.createRow((short) 5);
xlsInstance.createCell(wb, row4, (short) 0, "社員番号", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 1, "社員氏名", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 2, "入社日", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 3, "入社理由", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 4, "退社日", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 5, "退社理由", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 6, "転籍日", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 7, "転籍理由", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 8, "出向日", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 9, "出向理由", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 10, "異動日", bgColor, (short)10, true, "", false);
xlsInstance.createCell(wb, row4, (short) 11, "異動理由", bgColor, (short)10, true, "", false);

for (int i = 0; i < 10; i++) { 
HSSFRow rowT = sheet.createRow((short) 6 + i); 

xlsInstance.createCell(wb, rowT, (short) 0, "201001", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 1, "山田　直子", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 2, "2000/01/02", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 3, "中途入社", bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 4, "2000/01/02", bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 5, "転籍退社", bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 6, "2000/01/02", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 7, "転入", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 8, "2000/01/02", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 9, "出向受入解除",bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 10, "2000/01/02", bgColorNull, (short)10, true, "", false); 
xlsInstance.createCell(wb, rowT, (short) 11, "部門間異動", bgColorNull, (short)10, true, "", false); 
} 

String path = this.getServletContext().getRealPath("/")+"data/tyouhyou.xls"; 

FileOutputStream fileOut = new FileOutputStream(path); 

wb.write(fileOut); fileOut.close(); 
} catch (Exception ex) { 
ex.printStackTrace(); 
} 
} 

//下载帐票 
private void downloadFile(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { 
OutputStream os = res.getOutputStream(); 

try { 
String path = this.getServletContext().getRealPath("/") + "data/tyouhyou.xls"; 

FileInputStream hFile = new FileInputStream(path); 
BufferedInputStream bis = new BufferedInputStream(hFile); // レスポンス設定 
res.setContentType("application/octet-stream;charset=Windows-31J"); 
res.setHeader("Content-Disposition","attachment; filename=tyouhyou.xls"); 

int len = 0; 
byte[] buffer = new byte[1024]; 

while ((len = bis.read(buffer)) >= 0) {
os.write(buffer, 0, len);
}

bis.close();
} catch (IOException e) {
printOutNotFound(res);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {

} finally {
os = null;
}
}
}
}

//帐票文件未找到时的处理
private void printOutNotFound(HttpServletResponse res) {
try {
OutputStream toClient = res.getOutputStream();
res.setContentType("text/html;charset=utf-8");
toClient.write("File not found".getBytes());
toClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

二：服务器端不生成Excel文件而是将文件流直接在客户端下载

package excelDownLoad;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpServlet;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import veriserve.common.F30Excel;

public class ExcelDownLoadServlet extends HttpServlet{
private static final long serialVersionUID = 1L;
private short bgColorNull = HSSFColor.WHITE.index;
private short bgColor = HSSFColor.GREY_25_PERCENT.index;

public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
ArrayList titleList= new ArrayList();

titleList.add("社員番号");
titleList.add("社員氏名");
titleList.add("入社日");
titleList.add("入社理由");
titleList.add("退社日");
titleList.add("退社理由");
titleList.add("転籍日");
titleList.add("転籍理由");
titleList.add("出向日");
titleList.add("出向理由");
titleList.add("異動日");
titleList.add("異動理由");

ArrayList dataList= new ArrayList();

for (int i = 0; i < 6; i++) {
ArrayList rowList= new ArrayList();

rowList.add("201001");
rowList.add("筒井　功");
rowList.add("2005/10/25");
rowList.add("転籍入社");
rowList.add("2009/09/31");
rowList.add("転籍退社");
rowList.add("2005/10/25");
rowList.add("転籍理由");
rowList.add("2005/10/25");
rowList.add("出向理由");
rowList.add("2005/10/25");
rowList.add("異動理由");

dataList.add(rowList);
}

//帐票下载
downloadFile(titleList,dataList,request,response);
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
doPost(request,response);
}

//帐票下载
private void downloadFile(ArrayList titleList, ArrayList dataList,HttpServletRequest request, HttpServletResponse response) {
try {
F30Excel xlsInstance = F30Excel.getInstance();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("人事");
sheet.setDefaultColumnWidth((short) (10.82));// デフォルトの列幅の設定

//セル幅
// sheet.setColumnWidth((short)0,(short)5120);//140ピクセル
// sheet.setColumnWidth((short)1,(short)7680);//210ピクセル
// sheet.setColumnWidth((short)2,(short)7680);//210ピクセル

HSSFRow row = sheet.createRow((short) 0);

//タイトル
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 9));
xlsInstance.createCell(wb, row, (short) 0, "人事情報管理システム", bgColorNull, (short)19,false,"CENTER",false);

//日付
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy/MM/dd HH:mm");
String dateStr = sdf1.format(new Date());

sheet.addMergedRegion(new Region(0, (short) 10, 0, (short) 11));
xlsInstance.createCell(wb, row, (short) 10, "帳票出力：" + dateStr, bgColorNull, (short)10,false,"RIGHT",false);

HSSFRow row2 = sheet.createRow((short) 2);

xlsInstance.createCell(wb, row2, (short) 0, "検索入社理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 1, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 2, "検索退職理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 3, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 4, "検索転籍理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 5, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 6, "検索出向理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 7, "", bgColorNull, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 8, "検索異動理由", bgColor, (short)10,true,"",false);
xlsInstance.createCell(wb, row2, (short) 9, "", bgColorNull, (short)10,true,"",false);

HSSFRow row3 = sheet.createRow((short) 4);
sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) 1));
xlsInstance.createCell(wb, row3, (short) 0, "人事情報照会一覧", bgColorNull, (short)10.82,false,"",true);

//※情報一覧リストタイトルの行番号********************************
short mainRowIndex = 5;

HSSFRow row4 = sheet.createRow(mainRowIndex);

for(int i=0;i < titlelist.size(); i++){
xlsInstance.createCell(wb, row4, (short) i, (String)titleList.get(i), bgColor, (short)10, true, "", false);
}

for (int i = 0; i < dataList.size(); i++) {
HSSFRow rowT = sheet.createRow(mainRowIndex + 1 + i);

ArrayList rowList = (ArrayList)dataList.get(i);

xlsInstance.createCell(wb, rowT, (short) 0, (String)rowList.get(0), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 1, (String)rowList.get(1), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 2, (String)rowList.get(2), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 3, (String)rowList.get(3), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 4, (String)rowList.get(4), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 5, (String)rowList.get(5), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 6, (String)rowList.get(6), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 7, (String)rowList.get(7), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 8, (String)rowList.get(8), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 9, (String)rowList.get(9), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 10, (String)rowList.get(10), bgColorNull, (short)10, true, "", false);
xlsInstance.createCell(wb, rowT, (short) 11, (String)rowList.get(11), bgColorNull, (short)10, true, "", false);
}

// EXCELのコンテントタイプを設定
response.setContentType("application/msexcel");

// ファイル名に日本語を使うなら、下記のようにエンコードする
byte[] sjis = "帳票.xls".getBytes("Shift_JIS");
String fname = new String(sjis, "ISO8859_1");

// ファイル名を設定
response.setHeader("Content-Disposition", "attachment; filename=" + fname);

OutputStream out = response.getOutputStream(); 
wb.write(out);
out.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)