使用Java编写Excel传统课表转日历格式

今天遇到一个让她比较苦恼的事情，她手上有一大堆传统Excel格式的课程安排表，现在想将他们全搞成日历格式的，她只能手动一个一个搞，效率很低也很无聊，帮她做了个小工具，通过工具实现格式转换，解放双手！

原来的数据格式是这样：

转完的格式是这样的：

思路：
1.原表格中的数据可以抽象成一个类，类中的属性对应原表格中的列。

package xiaming.chen.domain;/** * Created by xiamingchen on 2017/7/1. */public class Info {    private String date;    private String timeBegin;    private String course;    private int hours;    private String teacher;    public String getDate() {        return date;    }    public void setDate(String date) {        this.date = date;    }    public String getTimeBegin() {        return timeBegin;    }    public void setTimeBegin(String timeBegin) {        this.timeBegin = timeBegin;    }    public String getCourse() {        return course;    }    public void setCourse(String course) {        this.course = course;    }    public int getHours() {        return hours;    }    public void setHours(int hours) {        this.hours = hours;    }    public String getTeacher() {        return teacher;    }    public void setTeacher(String teacher) {        this.teacher = teacher;    }    @Override    public String toString() {        return course + "-" + teacher;    }}

2.先将原表格中的数据读取出来，生成一堆的Info对象。
3.初始化新的Excel，将第一列的课表时间安排写入。
4.将日期写入表格，并返回日期总数
5.将每节课的信息填入表内

package xiaming.chen;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.*;import xiaming.chen.domain.Info;import xiaming.chen.util.TimeFormatUtil;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.Date;import java.util.List;/** * 为鸭蛋私人订制的Excel转换工具 */public class ExcelConvertor {    private static final String inputFile = "/Users/xiamingchen/Desktop/test.xlsx";    private static final String outputFile = "/Users/xiamingchen/Downloads/test/test.xlsx";    private static final int rowBeginNum = 4;    private static String line1, line2, line3;    public static void main(String[] args) throws IOException, InvalidFormatException {        List<Info> infoList = parseInfoFromInputFile(inputFile, rowBeginNum);        initExcel(outputFile);        List<String> dateList = writeDateToExcel(outputFile, infoList);        writeTitleToExcel(outputFile, dateList.size());        writeInfoToExcel(outputFile, infoList, dateList);    }    private static void writeTitleToExcel(String destPath, int columnNum) throws IOException {        FileInputStream fis = new FileInputStream(destPath);        XSSFWorkbook wb = new XSSFWorkbook(fis);        XSSFSheet sheet = wb.getSheetAt(0);        XSSFRow row1 = sheet.createRow(0);        createTitleRow(row1, line1, wb);        XSSFRow row2 = sheet.createRow(1);        createTitleRow(row2, line2, wb);        XSSFRow row3 = sheet.createRow(2);        createTitleRow(row3, line3, wb);        CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 0, columnNum);        CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 0, columnNum);        CellRangeAddress cellRangeAddress3 = new CellRangeAddress(2, 2, 0, columnNum);        sheet.addMergedRegion(cellRangeAddress1);        sheet.addMergedRegion(cellRangeAddress2);        sheet.addMergedRegion(cellRangeAddress3);        fis.close();        FileOutputStream fos = new FileOutputStream(destPath);        wb.write(fos);        fos.close();    }    private static XSSFRow createTitleRow(XSSFRow row, String value, XSSFWorkbook wb) {        XSSFCell cell = row.createCell(0);        cell.setCellValue(value);        XSSFCellStyle cellStyle = wb.createCellStyle();        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);        cellStyle.setAlignment(HorizontalAlignment.CENTER);        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);        cell.setCellStyle(cellStyle);        return row;    }    private static void writeInfoToExcel(String destPath, List<Info> infoList, List<String> dateList) throws IOException {        FileInputStream fis = new FileInputStream(destPath);        XSSFWorkbook wb = new XSSFWorkbook(fis);        XSSFSheet sheet = wb.getSheetAt(0);        for (int i=0; i<infoList.size(); ++i) {            Info info = infoList.get(i);            int column = dateList.indexOf(info.getDate()) + 1; //+1是因为第一列不是有效列            int rowOfBegin = getIndexOfBeginTime(info.getTimeBegin(), sheet);            int rowOfEnd = rowOfBegin + info.getHours() * 2 - 1;            XSSFRow row = sheet.getRow(rowOfBegin);            XSSFCell cell = row.createCell(column);            System.out.println(info.getDate() + " - " + column);            cell.setCellValue(info.toString());            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowOfBegin, rowOfEnd, column, column);            sheet.addMergedRegion(cellRangeAddress);            XSSFCellStyle cellStyle = wb.createCellStyle();            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);            cellStyle.setAlignment(HorizontalAlignment.CENTER);            cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);            cell.setCellStyle(cellStyle);            sheet.autoSizeColumn(column, true);        }        fis.close();        FileOutputStream fos = new FileOutputStream(destPath);        wb.write(fos);        fos.close();    }    private static List<String> writeDateToExcel(String destPath, List<Info> infoList) throws IOException, InvalidFormatException {        List<String> dateList = new ArrayList<String>();        FileInputStream fis = new FileInputStream(destPath);        XSSFWorkbook wb = new XSSFWorkbook(fis);        XSSFSheet sheet = wb.getSheetAt(0);        //生成第一行 日期        int column = 1;//因为第一(0)行无效，所以从第二(1)行开始        for (int i = 0; i < infoList.size(); ++i) {            if (dateList.contains(infoList.get(i).getDate())) {                continue;            }            XSSFRow dateRow;            if (i == 0) {                dateRow = sheet.createRow(3);            } else {                dateRow = sheet.getRow(3);            }            XSSFCell dateCell = dateRow.createCell(column);            dateCell.setCellValue(infoList.get(i).getDate());            column++;            dateList.add(infoList.get(i).getDate());        }        fis.close();        FileOutputStream fos = new FileOutputStream(destPath);        wb.write(fos);        fos.close();        return dateList;    }    private static int getIndexOfBeginTime(String beginTime, XSSFSheet sheet) {        int sum = sheet.getLastRowNum();        for (int i=rowBeginNum; i<=sum; ++i) {            String eachTime = sheet.getRow(i).getCell(0).getStringCellValue();            if (beginTime.equals(eachTime)) {                return i;            }        }        return -1;    }    private static void initExcel(String destPath) throws IOException {        float start = 8.5F;//每天最早八点半开始        float end = 21.0F;//每天最晚21点结束        int rowNum = rowBeginNum;//空4行，从第5行开始往下排时间        FileOutputStream fos = new FileOutputStream(destPath);        XSSFWorkbook wb = new XSSFWorkbook();        XSSFSheet sheet = wb.createSheet();        for (float i = start; i <= end; i = i + 0.5F, rowNum++) {            XSSFRow row = sheet.createRow(rowNum);            XSSFCell time = row.createCell(0);            int minute = (int) (i * 10) % 10;            String minuteStr = (minute == 5 ? "30" : "00");            time.setCellValue((int) i + ":" + minuteStr);        }        wb.write(fos);        fos.close();    }    private static List<Info> parseInfoFromInputFile(String inputFilePath, int rowBegin) throws IOException {        List<Info> infoList = new ArrayList<Info>();        FileInputStream fileInput = new FileInputStream(inputFilePath);        XSSFWorkbook wb = new XSSFWorkbook(fileInput);        XSSFSheet sheet = wb.getSheetAt(0);        int lastRowNum = sheet.getLastRowNum(); //内容的最后一行的行数        XSSFRow row1 = sheet.getRow(0);        line1 = row1.getCell(0).getStringCellValue();        XSSFRow row2 = sheet.getRow(1);        line2 = row2.getCell(0).getStringCellValue();        XSSFRow row3 = sheet.getRow(2);        line3 = row3.getCell(0).getStringCellValue();        for (int i = rowBegin; i < lastRowNum; ++i) {            XSSFRow row = sheet.getRow(i);            if (row.getCell(0).getDateCellValue() == null) { //遇到无效行，结束                break;            }            Info info = new Info();            Date date = row.getCell(0).getDateCellValue();            info.setDate(TimeFormatUtil.formatDate(date));            int hours = (int) row.getCell(3).getNumericCellValue();            info.setHours(hours);            String time = row.getCell(1).getStringCellValue();            String[] times = time.split("-");            info.setTimeBegin(TimeFormatUtil.formatTime(times[0]));            String course = row.getCell(2).getStringCellValue();            info.setCourse(course);            String teacher = row.getCell(4).getStringCellValue();            info.setTeacher(teacher);            infoList.add(info);        }        wb.close();        fileInput.close();        return infoList;    }}

还有一个处理时间和日期的工具类：

package xiaming.chen.util;import java.text.SimpleDateFormat;import java.util.Date;/** * Created by xiamingchen on 2017/7/1. */public class TimeFormatUtil {    public static String formatDate(Date date) {        SimpleDateFormat format = new SimpleDateFormat("MM/dd");        return format.format(date);    }    public static String formatTime(String time) {        int hour = Integer.parseInt(time.split(":")[0]);        String minute = time.split(":")[1];        if (hour < 8) { //小时数比8小，那就都是下午的            hour = hour + 12;        }        return hour + ":" + minute;    }}