Java POI导出excel经典实现 测试（获取工作日列表+导出Excel）

Java使用poi组件导出excel报表，能导出excel报表的还可以使用jxl组件，但jxl想对于poi功能有限，jxl应该不能载excel插入浮动层图片，poi能很好的实现输出excel各种功能，介绍poi导出excel功能实现案例,算比较常用的功能实现以及导出excel需要注意的地方，采用的是poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-scratchpad-3.8-20120326.jar

1.获取工作日

package com.zhangpan.util.holiday;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.List;/** * 1. 获取工作日期范围  * 2. 筛选出工作日（周六-周日不算） * 3. 手动插入计算节假日：维护 【工作休息日】 && 【休息工作日】(比如假期周4~周6，那么周日要加班) */public class ValidateHoliday {public SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");public ValidateHoliday() {}public ValidateHoliday(boolean isShowConsole, String beginDate, String endDate) throws ParseException {ValidateHoliday.isShowConsole = isShowConsole;this.beginDate = formatter.parse(beginDate);this.endDate = formatter.parse(endDate);}private static boolean isShowConsole = false;// 是否显示控制台private Date beginDate;// 开始日期private Date endDate;  // 结束日期private int workdaySum;// 工作日天数private int holidaySum;// 休息日天数public void setIsShowConsole(boolean flag) {ValidateHoliday.isShowConsole = flag;}public void setBeginDate(Date beginDate) {this.beginDate = beginDate;}public void setEndDate(Date endDate) {this.endDate = endDate;}public int getWorkdaySum() {return workdaySum;}public int getHolidaySum() {return holidaySum;}public static void ConsolePrint(String str) {if (isShowConsole) {System.out.print(str);}}public static void ConsolePrintln(String str) {if (isShowConsole) {System.out.println(str);}}/** * 功能: 获取工作日 并标识 1工作日 | -1休息日  * 2017年9月1日下午3:43:37  * Author ZhangPan */public List getWorkingDay() {ConsolePrintln("开始工作日筛选...");// 用于存储工作日&工作标识的ListList list = new ArrayList();Calendar start = Calendar.getInstance();start.setTime(beginDate);Calendar end = Calendar.getInstance();end.setTime(endDate);while (start.compareTo(end) <= 0) {//获取星期标识周日 周一  周二 周三 周四  周六  1 2 3 4 5 6 7int day = start.get(Calendar.DAY_OF_WEEK);// 判断是否是周六周日 -1|1   int flag = (day == Calendar.SUNDAY || day == Calendar.SATURDAY) ? -1 : 1;//  1 ^ 7if (flag == 1) {workdaySum++;} else {holidaySum++;}String curDate = formatter.format(start.getTime()); // 当前日期HoliDayPo po=new HoliDayPo(curDate,flag);list.add(po);start.set(Calendar.DATE, start.get(Calendar.DATE) + 1);}ConsolePrintln("完成工作日筛选...");return list;}/** * 功能: 维护假期 * 2017年9月1日下午5:20:34 * Author ZhangPan */public  void replaceHoliday(List oldList,List newList) {for (HoliDayPo oldPo : oldList) {for(HoliDayPo newPo:newList) {if (oldPo.getDate().equals(newPo.getDate()) && oldPo.getIsWorkDay()!=newPo.getIsWorkDay()) {if(newPo.getIsWorkDay()==1) {holidaySum++;workdaySum--;}else {workdaySum++;holidaySum--;} oldPo.setIsWorkDay(newPo.getIsWorkDay());}}}}public static List doWork() throws ParseException{ValidateHoliday vhd = new ValidateHoliday(true, "2017-01-01", "2017-12-31");List list = vhd.getWorkingDay();//常规过滤for (HoliDayPo po : list) {ValidateHoliday.ConsolePrintln("日期过滤处理结果:[" + po.getDate() + "][" + po.getFormate() + "]");}ValidateHoliday.ConsolePrintln("******工作日天数:[" + vhd.getWorkdaySum() + "]******");ValidateHoliday.ConsolePrintln("******工作日天数:[" + vhd.getHolidaySum() + "]******");//插入节假日期List holidayList = new ArrayList();holidayList.add(new HoliDayPo("2017-01-01",1));holidayList.add(new HoliDayPo("2017-01-02",1));holidayList.add(new HoliDayPo("2017-01-03",1));holidayList.add(new HoliDayPo("2017-01-04",1));vhd.replaceHoliday(list,holidayList);//节假日替换后for (HoliDayPo po : list) {ValidateHoliday.ConsolePrintln("日期过滤处理结果:[" + po.getDate() + "][" + po.getFormate() + "]");}ValidateHoliday.ConsolePrintln("******工作日天数:[" + vhd.getWorkdaySum() + "]******");ValidateHoliday.ConsolePrintln("******工作日天数:[" + vhd.getHolidaySum() + "]******");return list;}public static void main(String[] args) throws ParseException {ValidateHoliday.doWork();}}package com.zhangpan.util.holiday;/** * PO类 * @author L460 * */public class HoliDayPo {public HoliDayPo() {}public HoliDayPo(String date,int isWorkDay) {this.date=date;this.isWorkDay=isWorkDay;}private String date;private int isWorkDay;private String formate;public String getDate() {return date;}public void setDate(String date) {this.date = date;}public int getIsWorkDay() {return isWorkDay;}public void setIsWorkDay(int isWorkDay) {this.isWorkDay = isWorkDay;}public String getFormate() {this.formate=this.getIsWorkDay() == 1 ? "是" : "否";return formate;}public void setFormate(String formate) {this.formate = formate;}@Overridepublic String toString() {return "HoliDayPo [date=" + date + ", isWorkDay=" + isWorkDay + "]";}}package com.zhangpan.util.holiday;/** * PO类 * @author L460 * */public class HoliDayPo {public HoliDayPo() {}public HoliDayPo(String date,int isWorkDay) {this.date=date;this.isWorkDay=isWorkDay;}private String date;private int isWorkDay;private String formate;public String getDate() {return date;}public void setDate(String date) {this.date = date;}public int getIsWorkDay() {return isWorkDay;}public void setIsWorkDay(int isWorkDay) {this.isWorkDay = isWorkDay;}public String getFormate() {this.formate=this.getIsWorkDay() == 1 ? "是" : "否";return formate;}public void setFormate(String formate) {this.formate = formate;}@Overridepublic String toString() {return "HoliDayPo [date=" + date + ", isWorkDay=" + isWorkDay + "]";}}

2.导出为Excel

package com.zhangpan.util.exportExcel;import java.awt.Color;import java.io.File;import java.io.FileOutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import org.apache.commons.io.FileUtils;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.HSSFPalette;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.ss.util.CellRangeAddress;import com.zhangpan.util.holiday.HoliDayPo;import com.zhangpan.util.holiday.ValidateHoliday;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.ClientAnchor;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Drawing;import org.apache.poi.ss.usermodel.Workbook;public class CreateSimpleExcelToDisk {/** * 导出excel * http://www.anyrt.com/blog/list/poiexcel.html  更多参考 * @param list * @param url * @return */public static void exportSimpleExcel(List list,String url) {try {//第一步 ，创建一个WebBook，对应一个Excel文件HSSFWorkbook wb = new HSSFWorkbook();//设置表头样式//创建一个居中格式    HSSFCellStyle style = wb.createCellStyle();    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);    //设置字体样式    HSSFFont font = wb.createFont();    //font.setItalic(true);//斜体    //font.setUnderline(HSSFFont.U_SINGLE);//下划线    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//黑体    font.setFontHeightInPoints((short)12);//字体高度    style.setFont(font);    //第二步，在webBook中创建一个sheet，对应excel文件中的sheet    HSSFSheet sheet = wb.createSheet("工作日列表");    //第三步，在sheet中添加表头第0行，注意老版本POI对excel的行数有限制short    //第一行    HSSFRow row= sheet.createRow(0);    HSSFCell cell= row.createCell(0);    cell.setCellStyle(style);    cell.setCellValue("某时间段内的工作日列表");       //第二行    row= sheet.createRow(1);        //第四步，创建单元格，并设置值表头，        cell= row.createCell(0);    cell.setCellStyle(style);    cell.setCellValue("日期");    cell= row.createCell(1);    cell.setCellStyle(style);    cell.setCellValue("是否工作日");    cell= row.createCell(2);    cell.setCellStyle(style);    cell.setCellValue("操作时间");        //设置颜色    short colorIndex = 10;    HSSFPalette palette = wb.getCustomPalette();//自定义颜色    //绿色    Color rgb = Color.GREEN;    short bgIndex = colorIndex ++; //背景颜色下标值    palette.setColorAtIndex(bgIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());    //黑色    short bdIndex = colorIndex ++; //边框颜色下标值    rgb = Color.BLACK;    palette.setColorAtIndex(bdIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());    //红色    short flagIndex = colorIndex ++; //不是工作日的标识颜色  区别开来    rgb = Color.RED;    palette.setColorAtIndex(flagIndex, (byte) rgb.getRed(), (byte) rgb.getGreen(), (byte) rgb.getBlue());        //第六步  写入实体数据，实际工作环境数据是从数据库中导出    for(int i=0;i list=ValidateHoliday.doWork();CreateSimpleExcelToDisk.exportSimpleExcel(list,"E:/holidayList.xls");}}package com.zhangpan.util.exportExcel;public class MSExcelUtil {     public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;    public static final int UNIT_OFFSET_LENGTH = 7;    public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };     /**     * pixel units to excel width units(units of 1/256th of a character width)     *      * @param pxs     * @return     */    public static short pixel2WidthUnits(int pxs) {        short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));        widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];        return widthUnits;    }     /**     * excel width units(units of 1/256th of a character width) to pixel units     *      * @param widthUnits     * @return     */    public static int widthUnits2Pixel(int widthUnits) {        int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH;        int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;        pixels += Math.round(offsetWidthUnits                / ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));         return pixels;    }}

3.效果图