com.shiyq.poi.HSSFTest.java Source code

Introduction

Here is the source code for com.shiyq.poi.HSSFTest.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.shiyq.poi;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;
import java.util.Objects;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 *
 * @author admin
 */
public class HSSFTest {

    public static void main(String[] args) {
        String excelName = "d:/temp/???.xls";

        boolean created = createExcel(excelName);
        if (created == true) {
            System.out.println("Excel" + excelName + " ?");
        } else {
            System.out.println("Excel" + excelName + " ");
        }
    }

    public static boolean createExcel(String excelName) {
        boolean created = false;
        Workbook wb = new HSSFWorkbook();
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headStyle = wb.createCellStyle();
        headStyle.setFont(font);

        Sheet sheet = wb.createSheet("20165???");
        String[] head = { "??", "?", "??", "???", "????",
                "????", "?", "??", "", "",
                "??" };
        String[] code = { "card_no", "card_type", "spread_time", "spread_emp_no", "spread_emp_name", "owner_name",
                "plate", "blance", "start_time", "end_time", "month_money" };

        List<Map<String, Object>> list = setList();
        setSheet(sheet, list, head, headStyle, code);
        //
        int startRow = 4;
        int endRow = 8;
        int startColumn = head.length + 4;
        int endColumn = head.length + 8;

        String describe = "1?" + (new Date().toString()) + "\n";
        describe += "2.\n";
        describe += "3.?2016-4-42016-5-4";
        CellStyle descStyle = wb.createCellStyle();
        descStyle.setAlignment(CellStyle.ALIGN_LEFT);
        descStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        setSheet(sheet, startRow, endRow, startColumn, endColumn, describe, descStyle);

        Sheet sheet1 = wb.createSheet("20166???");
        setSheet(sheet1, list, head, headStyle, code);
        setSheet(sheet1, startRow, endRow, startColumn, endColumn, describe, descStyle);
        try {
            try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
                wb.write(fileOut);
                created = true;
            }
        } catch (FileNotFoundException ex) {
            Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
        }
        return created;
    }

    public static void setHeadRow(Row row, String[] cells, int startColumn, CellStyle style) {
        for (int i = 0; i < cells.length; i++) {
            row.createCell(i + startColumn).setCellValue(cells[i]);
            row.getCell(i + startColumn).setCellStyle(style);
        }
    }

    public static void setHeadRow(Row row, String[] cells, CellStyle style) {
        setHeadRow(row, cells, 0, style);
    }

    public static List setList() {

        List<Map<String, Object>> list = new LinkedList<>();

        for (int i = 0; i < 100; i++) {
            Map<String, Object> map = new HashMap();
            map.put("card_no", "1001" + i);
            String card_type = "?";
            if (i % 2 == 0) {
                card_type = "?";
            }
            map.put("card_type", card_type);
            map.put("spread_time", "2016-4-3 13:23:21");
            map.put("spread_emp_no", "0001");
            map.put("spread_emp_name", "");
            map.put("owner_name", "?");
            map.put("plate", "?B-1001-1");
            map.put("blance", "1000.00");
            map.put("start_time", "2016-4-3");
            map.put("end_time", "2016-5-3");
            map.put("month_money", "200.00");
            list.add(map);
        }
        return list;
    }

    private static void setSheet(Sheet sheet, int startRow, int startColumn, List<Map<String, Object>> list,
            String[] head, CellStyle headStyle, String[] code) {
        Row row = sheet.createRow(startRow);
        setHeadRow(row, head, startColumn, headStyle);
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            row = sheet.createRow(i + startRow + 1);
            setRow(row, startColumn, map, code);
        }

    }

    private static void setSheet(Sheet sheet, List<Map<String, Object>> list, String[] head, CellStyle headStyle,
            String[] code) {
        setSheet(sheet, 0, 0, list, head, headStyle, code);
    }

    private static void setSheet(Sheet sheet, int startRow, int endRow, int startColumn, int endColumn,
            String describe, CellStyle descStyle) {
        Row row = sheet.getRow(startRow);
        if (Objects.isNull(row)) {
            row = sheet.createRow(startRow);
        }
        Cell cell = row.createCell(startColumn);
        cell.setCellStyle(descStyle);
        cell.setCellValue(describe);
        sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startColumn, endColumn));
    }

    private static void setRow(Row row, int startColumn, Map<String, Object> map, String[] code) {
        for (int i = 0; i < code.length; i++) {
            row.createCell(i + startColumn).setCellValue((String) map.get(code[i]));
        }
    }

    private static void setRow(Row row, Map<String, Object> map, String[] code) {
        setRow(row, 0, map, code);
    }

}