poi 导出excel 简单示例

 

 

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @Auyher: zjk
 */
@RestController
@RequestMapping("/weather")
public class Weather {

    /**
     * @return 天气查询-返回历史30天的天气信息
     * @throws IOException
     */
    @GetMapping("export")
    public void export(@RequestParam("site") String site, HttpServletResponse response) throws Exception {

        ArrayList arrayList = new ArrayList();

        Document doc = null;
        doc = Jsoup.connect("https://www.tianqishi.com/lishi/" + site + ".html").get();

        Element body = doc.getElementsByClass("yuBaoTable").get(0);
        Elements div = body.select("tr");

        for (Element v : div) {
            LinkedHashMap<String, String> map = new LinkedHashMap<>();
            Element when = v.select("td").get(0);
            String whenStr = when.select("a").get(0).text();
            String s = whenStr.substring(0, 4) + "-" + whenStr.substring(4, 6) + "-" + whenStr.substring(6, 8);
            map.put("when", s);
            map.put("temp", v.select("td").get(1).text());
            map.put("wind", v.select("td").get(2).text());
            arrayList.add(map);
        }
        List list = arrayList.subList(0, 30);


        // 创建工作簿类
        XSSFWorkbook workbook = new XSSFWorkbook();


        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font headerFont = workbook.createFont();
        // 设置字体
        headerFont.setFontName("宋体");
        // 设置字体大小
        headerFont.setFontHeightInPoints((short) 12);
        // 字体加粗
        headerFont.setBold(true);
        // 斜体
        headerFont.setItalic(false);
        // 字体颜色
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        //设置字体高度
        headerFont.setFontHeightInPoints((short) 22);

        // 创建工作表并设置表名
        XSSFSheet sheet = workbook.createSheet("历史天气信息");

        //设置列宽和行高
        sheet.setDefaultRowHeight((short) 700);
        sheet.setDefaultColumnWidth(25);

        //创建表头
        XSSFRow row = sheet.createRow(0);

        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(region);

        Cell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("近一个月天气情况");

        CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
        CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
        CellUtil.setFont(cell, headerFont);

        // 3.设置合并单元格边框
        setBorderStyle(sheet, region);
        XSSFRow row2 = sheet.createRow(1);
        XSSFCell cell0 = row2.createCell(0);
        XSSFCell cell1 = row2.createCell(1);
        XSSFCell cell2 = row2.createCell(2);
        XSSFCell cell3 = row2.createCell(3);
        cell0.setCellValue("序号");
        cell1.setCellValue("日期");
        cell2.setCellValue("天气");
        cell3.setCellValue("温度");

        cell0.setCellStyle(style);
        cell1.setCellStyle(style);
        cell2.setCellStyle(style);
        cell3.setCellStyle(style);

        for (int i = 0; i < list.size(); i++) {
            LinkedHashMap<String, String> map = (LinkedHashMap<String, String>) list.get(i);
            XSSFRow row1 = sheet.createRow(i + 2);
            XSSFCell cell4 = row1.createCell(0);
            XSSFCell cell5 = row1.createCell(1);
            XSSFCell cell6 = row1.createCell(2);
            XSSFCell cell7 = row1.createCell(3);
            cell4.setCellValue(i + 1);
            cell5.setCellValue(map.get("when"));
            cell6.setCellValue(map.get("wind"));
            cell7.setCellValue(map.get("temp"));

            cell4.setCellStyle(style);
            cell5.setCellStyle(style);
            cell6.setCellStyle(style);
            cell7.setCellStyle(style);
        }

        try{
            String fileName = "历史天气信息.xlsx";
            ServletOutputStream out = response.getOutputStream();
            response.setHeader("content-type", "text/plain");
            response.setHeader("content-type", "application/x-msdownload;");
            response.setContentType("text/plain; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="  +  URLEncoder.encode(fileName, "UTF-8"));
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}