项目里用的excel下载功能.记下以后有用.
主要是一个xlsCreator
public class MyXlsCreator {
@SuppressWarnings("unused")
private static Logger logger = Logger.getLogger(MyXlsCreator.class);
private SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
private HSSFWorkbook wb;
private CellStyle alignCenter;
private CellStyle alignRight;
private CellStyle dateStyle;
private CellStyle topBorder;
private CellStyle totalTopBorder;
private CellStyle money;
private CellStyle fontStyle;
private CellStyle bigTitleStyle;
private MySheet[] mySheets;
public MyXlsCreator(MySheet ...mySheets){
this.wb= new HSSFWorkbook();
this.mySheets=mySheets;
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);// 设置字体大小
alignCenter=wb.createCellStyle();//居中对齐的样式
alignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中对齐的样式
alignCenter.setFont(font);
alignRight=wb.createCellStyle();
alignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
alignRight.setFont(font);
dateStyle=wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd"));
dateStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dateStyle.setFont(font);
topBorder=wb.createCellStyle();
topBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
topBorder.setFont(font);
totalTopBorder=wb.createCellStyle();
totalTopBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
totalTopBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
totalTopBorder.setDataFormat(format.getFormat("#,##0.00"));
totalTopBorder.setFont(font);
money=wb.createCellStyle();
money.setDataFormat(format.getFormat("#,##0.00"));
money.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
money.setFont(font);
fontStyle=wb.createCellStyle();
fontStyle.setFont(font);
HSSFFont bigTitleFont = wb.createFont();
bigTitleFont.setFontName("宋体");
bigTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
bigTitleFont.setFontHeightInPoints((short)22);
bigTitleStyle=wb.createCellStyle();
bigTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
bigTitleStyle.setFont(bigTitleFont);
init();
}
@SuppressWarnings("deprecation")
private void init(){
for(MySheet each:mySheets){
List<MyTab> tabs=each.getTabs();
HSSFSheet sheet=wb.createSheet(each.getSheetName());
sheet.setDefaultColumnWidth(15);
int col=6;//默认列数
try {//找出第一个表的列数
col=each.getTabs().get(0).getHeads().length;
} catch (Exception e) {
}
int totalrow=0;
if(each.getBigTitle()!=null){
HSSFRow row=sheet.createRow(totalrow);
sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1)));
row.setHeightInPoints(26);
Cell cell=row.createCell(0);
cell.setCellValue(each.getBigTitle());
cell.setCellStyle(bigTitleStyle);
totalrow++;
}
for(MyTab eachTab:tabs){
//写标题
if(eachTab.getTitleList()!=null){
for(int ti=0;ti<eachTab.getTitleList().length;ti++){
HSSFRow row=sheet.createRow(totalrow);
sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1)));
totalrow++;
Cell cell=row.createCell(0);
cell.setCellValue(eachTab.getTitleList()[ti]);
cell.setCellStyle(fontStyle);
}
}
if(eachTab.getHeads()!=null){
//写表头
HSSFRow row=sheet.createRow(totalrow);
totalrow++;
for(int hi=0;hi<eachTab.getHeads().length;hi++){
HSSFCell cell= row.createCell(hi);
cell.setCellStyle(alignCenter);
cell.setCellValue(eachTab.getHeads()[hi]);
}
}
if(eachTab.getItems()!=null){
//写数据
for(Object[] rowData:eachTab.getItems()){
HSSFRow row1=sheet.createRow(totalrow);
totalrow++;
for(int di=0;di<rowData.length;di++){
Object data=rowData[di];
if(data==null){
continue;
}
Cell cell=row1.createCell(di);
if(data instanceof Integer){
cell.setCellValue((Integer)data);
cell.setCellStyle(alignCenter);
}
if(data instanceof Double){
cell.setCellValue((Double)data);
cell.setCellStyle(money);
}
if(data instanceof String){
cell.setCellValue((String)data);
cell.setCellStyle(alignCenter);
}
if(data instanceof Date){
Date date = null;
try {
date = sdf.parse(sdf.format((Date)data));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
cell.setCellValue(date);
cell.setCellStyle(dateStyle);
}
}
}
}
}
}
}
public void write(String path) throws IOException{
File outPutFile=new File(path);
if(!outPutFile.exists()){
outPutFile.createNewFile();
}
FileOutputStream os = new FileOutputStream(outPutFile);
wb.write(os);
os.close();
}
public void write(OutputStream outputStream) throws IOException{
wb.write(outputStream);
outputStream.close();
}
}
具体实现的时候用于创建sheet,在xlsCreator构造函数中调用,初始化表
public MySheet getPersonalInfo(){
MySheet mySheet= new MySheet();
mySheet.setSheetName("个人信息");
MyTab tab=new MyTab();
tab.setTitleList(new String[]{我的个人信息});
tab.setHeads(new String[]{"姓名","手机号码","身份证号码","年龄","性别"});
Object[][] datas=new Object[xls.size()][5];
if(xls.size()>0){
for(int i=0;i<xls.size();i++){
datas[i][0]="吴星";
datas[i][1]="135****2682";
datas[i][2]="360***3412";
datas[i][3]="18";
datas[i][4]="男";
}
}
tab.setItems(datas);
mySheet.addTab(tab);
return mySheet;
}
public class MySheet {
@SuppressWarnings("unused")
private static Logger logger = Logger.getLogger(MySheet.class);
private String sheetName;
private String bigTitle;
private List<MyTab> tabs;
public MySheet(){
tabs=new ArrayList<MyTab>();
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public String getBigTitle() {
return bigTitle;
}
public void setBigTitle(String bigTitle) {
this.bigTitle = bigTitle;
}
public List<MyTab> getTabs() {
return tabs;
}
public void setTabs(List<MyTab> tabs) {
this.tabs = tabs;
}
public void addTab(MyTab tab){
tabs.add(tab);
}
public void addTabs(Collection<MyTab> tabs){
this.tabs.addAll(tabs);
}
}
demo 如下
public void DLInvestDetail(HttpServletResponse response){
MyXlsCreator creator=new MyXlsCreator(xlsServiceImpl.getPersonalInfo());
String fileName = sdf.format(new Date())+"个人信息详情.xls";
try {
fileName = URLEncoder.encode(fileName, "utf-8");
} catch (UnsupportedEncodingException e1) {
logger.error(e1.getMessage(),e1.fillInStackTrace());
}
response.setContentType("application/x-download;charset=utf-8");
response.addHeader("Content-Disposition","attachment;filename=" + fileName);
OutputStream outputStream;
try {
outputStream = response.getOutputStream();
creator.write(outputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}