java导出excel设置表头

import java.sql.SQLException;
import java.util.List;
 
import javax.servlet.http.HttpServletRequest;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.CellRangeAddress;
 
public class Excel {
 
         private HSSFWorkbook wb = null;
 
         private HSSFSheet sheet = null;
 
         private HSSFRow row = null;
 
         private HSSFCell cell = null;
 
         private HSSFCellStyle titleStyle = null;
 
         private HSSFCellStyle headStyle = null;
 
         private HSSFCellStyle bodyStyle = null;
 
         private int num = 0;
         
         private int headLength = 0;
 
         public Excel() {
                   wb = new HSSFWorkbook();
                   sheet = wb.createSheet();
                   sheet.setDefaultColumnWidth(14);
                   sheet.setDefaultRowHeight((short)20);
                   //打印设置
                   HSSFPrintSetup hps = sheet.getPrintSetup(); 
                   hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 设置A4纸
                   hps.setLandscape(true); // 将页面设置为横向打印模式
//               sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
//               sheet.setVerticallyCenter(true); // 设置打印页面为垂直居中
                   //冻结第一行和第二行
                   sheet.createFreezePane( 0, 2, 0, 2 );
 
                   
                   init();
         }
         
         /**
          * 初始化样式
          */
         private void init() {
                   titleFont();
                   headFont();
                   bodyFont();
         }
 
         /**
          * 设置标题样式
          * 
          */
         private void titleFont() {
                   HSSFFont titleFont = wb.createFont();
                   titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                   titleFont.setFontName("宋体");
                   titleFont.setFontHeightInPoints((short) 18);
                   titleStyle = wb.createCellStyle();
                   titleStyle.setFont(titleFont);
//               titleStyle.setBorderTop((short)1);
//               titleStyle.setBorderRight((short)1);
                   titleStyle.setBorderBottom((short)1);
//               titleStyle.setBorderLeft((short)1);
                   
                   titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 
         }
 
         /**
          * 设置head样式
          * 
          */
         private void headFont() {
 
                   HSSFFont headFont = wb.createFont();
                   headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                   headFont.setFontName("宋体");
                   headFont.setFontHeightInPoints((short) 11);
                   headStyle = wb.createCellStyle();
                   headStyle.setFont(headFont);
                   headStyle.setBorderTop((short)1);
                   headStyle.setBorderRight((short)1);
                   headStyle.setBorderBottom((short)1);
                   headStyle.setBorderLeft((short)1);
                   headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         }
 
         /**
          * 设置body样式
          * 
          */
         private void bodyFont() {
                   HSSFFont bodyFont = wb.createFont();
                   bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
                   bodyFont.setFontName("宋体");
                   bodyFont.setFontHeightInPoints((short) 9);
                   bodyStyle = wb.createCellStyle();
                   bodyStyle.setFont(bodyFont);
                   bodyStyle.setBorderTop((short)1);
                   bodyStyle.setBorderRight((short)1);
                   bodyStyle.setBorderBottom((short)1);
                   bodyStyle.setBorderLeft((short)1);
                   bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                   bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
         }
         
         /**
          * 生成Excel主方法
          * @param xml :包括title和head的内容和设置
          * @param list :内容
          * @param fit :是否需要宽度自适应
          * @return HSSFWorkbook
          * @throws SQLException
          */
         public HSSFWorkbook createWorkBook(final ExcelHeadXML xml, final List<Object[]> list, boolean fit) throws SQLException {
                   createTop(xml);
                   /*
                   List<Object[]> list1 = new LinkedList<Object[]>();
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   createBody(list1,true);
                   */
                   createBody(list,true);
                   //宽度自适应
                   if(fit){
                            for(int i=0; i<headLength; i++){
                                     sheet.autoSizeColumn(i); 
                            }
                   }
                   return wb;
         }
         
         /**
          * 生成Excel主方法
          * @param xml :包括title和head的内容和设置
          * @param list :内容
          * @param fit :是否需要宽度自适应
          * @return HSSFWorkbook
          * @throws SQLException
          */
         public HSSFWorkbook createWorkBook(final ExcelHeadXML xml,HttpServletRequest request, final List<Object[]> list, boolean fit) throws SQLException {
                   createTop(xml,request);
                   createBody(list,true);
                   //宽度自适应
                   if(fit){
                            for(int i=0; i<headLength; i++){
                                     sheet.autoSizeColumn(i); 
                            }
                   }
                   return wb;
         }
         
         /**
          * 创建Excel的Head和title部分
          * @param xml ExcelHeadXML
          */
         @SuppressWarnings("deprecation")
         private void createTop(final ExcelHeadXML xml,HttpServletRequest request) {
                   //创建Title部分
                   if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
                            row = sheet.createRow(num++);
                            cell = row.createCell(0);
                            cell.setCellStyle(titleStyle);
                            sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
                                               xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
                                                                 .getUniteRowCount()));
                            
                   }
                   //创建Head部分
                   List<String> head = xml.getHead();
                   if(null != head && head.size() > 0){
                            headLength = xml.getHeadLength();
                            row = sheet.createRow(num++);
                            for(int i=0; i<head.size(); i++){
                                     cell = row.createCell(i);
                                     cell.setCellStyle(headStyle);
                                     cell.setCellValue(new HSSFRichTextString(head.get(i)));
                                     
                            }
                   }
         }
         
         
         /**
          * 创建Excel的Head和title部分
          * @param xml ExcelHeadXML
          */
         @SuppressWarnings("deprecation")
         private void createTop(final ExcelHeadXML xml) {
                   //创建Title部分
                   if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
                            row = sheet.createRow(num++);
                            cell = row.createCell(0);
                            cell.setCellStyle(titleStyle);
                            cell.setCellValue(new HSSFRichTextString(xml.getTitle().trim()));
                            sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
                                               xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
                                                                 .getUniteRowCount()));
                            
                   }
                   //创建Head部分
                   List<String> head = xml.getHead();
                   if(null != head && head.size() > 0){
                            headLength = xml.getHeadLength();
                            row = sheet.createRow(num++);
                            for(int i=0; i<head.size(); i++){
                                     cell = row.createCell(i);
                                     cell.setCellStyle(headStyle);
                                     cell.setCellValue(new HSSFRichTextString(head.get(i)));
                                     
                            }
                   }
         }
         /**
          * 创建Excel的body部分
          * @param list : body部分的数据
          * @param useId : body部分是否要序号
          * @throws SQLException 
          */
         private void createBody(final List<Object[]> list, boolean useId) throws SQLException {
                   if(null != list && list.size() > 0){
                            Object[] o;
                            
                            for(int i=0; i<list.size(); i++){
                                     row = sheet.createRow(num++);
                                     row.setHeightInPoints(30);
                                     o = list.get(i);
                                     if(useId){
                                               //序号
                                               cell = row.createCell(0);
                                               cell.setCellStyle(bodyStyle);
                                               cell.setCellValue(new HSSFRichTextString((i+1)+""));
                                               
                                     }
                                     for(int j=0; j<o.length; j++){
                                               if(useId){
                                                        cell = row.createCell(j+1);
                                               }else{
                                                        cell = row.createCell(j);
                                               }
                                               cell.setCellStyle(bodyStyle);
                                               cell.setCellValue(new HSSFRichTextString(emptyToString(o[j])));
                                               
                                     }
                            }
                   }
         } 
 
 
 
 
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值