用Java创建一个Excel

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.13</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.13</version>
</dependency>

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

public class CreateExcel {


    public static void main(String args[]) {
        CreateExcel createExcel = new CreateExcel();
        String path = "D:\\lll\\test.xlsx";//路径可随意替换
        try {
            //随意创建一个Excel
            createExcel.createExcel(path);
            //读取上一行创建的Excel
            createExcel.getExcel(path);
            System.out.println("----------我是分割线----------");
            //创建Excel的表头
            createExcel.createExcelTop(path);
            //读取上一行创建的Excel
            createExcel.getExcel(path);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //创建Excel文件
    public void createExcel(String path) throws Exception {
        //创建Excel文件对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //用文件对象创建sheet对象  
        HSSFSheet sheet = wb.createSheet("第一个sheet");
        //sheet对象创建行对象  
        HSSFRow row = sheet.createRow(0);
        //创建单元格样式     
        CellStyle cellStyle = wb.createCellStyle();
        //用行对象创建单元格对象Cell 
        Cell cell = row.createCell(0);
        //cell对象读写。设置Excel工作表的值
        cell.setCellValue(1);
        //输出Excel文件
        FileOutputStream output = new FileOutputStream(path);
        wb.write(output);
        output.flush();
    }

    //读取Excel文件的值
    public void getExcel(String path) throws Exception {

        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path));
        //得到Excel工作簿对象    
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        //得到sheet页个数(从1开始数,但是取值的时候要从index=0开始)
        int scount = wb.getNumberOfSheets();
        System.out.println("sheet页的个数为:" + (scount));
        for (int a = 0; a < scount; a++) {
            String sheetName = wb.getSheetName(a);
            System.out.println("" + (a + 1) + "sheet页的名字为" + sheetName + ",内容如下:");
            //得到Excel工作表对象(0代表第一个sheet)    
            HSSFSheet sheet = wb.getSheetAt(a);
            HSSFSheet sheet1 = wb.getSheet("第一个sheet");
            //预定义单元格的值
            String c = "";
            //得到工作表的有效行数(行数从0开始数,取值时从index=0开始)
            int rcount = sheet.getLastRowNum();
            System.out.println("" + (a + 1) + "sheet页有" + rcount + "");
            for (int i = 0; i <= rcount; i++) {
                //得到Excel工作表的行    
                HSSFRow row = sheet.getRow(i);
                if (null != row) {
                    //获取一行(row)的有效单元格(cell)个数(列数从1开始数,取值的时候从index=0开始取)
                    int ccount = row.getLastCellNum();
                    System.out.println("" + (i + 1) + "行有" + ccount + "个单元格");
                    for (int j = 0; j < ccount; j++) {
                        //得到Excel工作表指定行的单元格    
                        HSSFCell cell = row.getCell(j);
                        if (null != cell) {
                            //得到单元格类型
                            int cellType = cell.getCellType();
                            switch (cellType) {
                                //字符串类型  
                                case HSSFCell.CELL_TYPE_STRING:
                                    c = cell.getStringCellValue();
                                    if (c.trim().equals("") || c.trim().length() <= 0)
                                        c = " ";
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    c = String.valueOf(cell.getNumericCellValue());
                                default:
                                    break;
                            }
                            //String c = cell.getStringCellValue();
                            System.out.print("" + (i + 1) + "" + (j + 1) + "列的值为:" + c + "    ");
                        } else {
                            System.out.print("" + (i + 1) + "" + (j + 1) + "列的值为:空" + "    ");
                        }
                    }
                    System.out.println();
                } else {
                    System.out.println("" + (i + 1) + "行的值为:空");
                }
            }
        }
    }

    //创建excel的表头,设置字体及字体颜色,设置单元格填充色
    public void createExcelTop(String path) throws Exception {
        //创建Excel对象
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //Excel样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //Excel字体设置
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        hssfFont.setColor(HSSFColor.RED.index);
        style.setFont(hssfFont);
        //创建sheet对象
        Sheet sheet = hssfWorkbook.createSheet();
        //创建row
        Row row = sheet.createRow(1);
        //创建cell
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("年龄");
        row.createCell(2).setCellValue("入学日期");
        row.createCell(3).setCellValue("分数");

        //Excel设置单元格填充色
        HSSFCellStyle style2 = hssfWorkbook.createCellStyle();
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
        style2.setFillForegroundColor(HSSFColor.RED.index);//前景填充色

//        for (int i = 2; i < 4; i++) {
//            Row row2 = sheet.createRow(i);
//            row2.createCell(0).setCellValue("李明");
//            Cell cell2 = row2.createCell(1);
//            cell2.setCellValue("21");
//            cell2.setCellStyle(style2);
//            row2.createCell(2).setCellValue("20170101");
//            row2.createCell(3).setCellValue(78);
//        }
        for (int i = 2; i < 4; i++) {
            Row row2 = sheet.createRow(i);
            row2.createCell(0).setCellValue("李明");
            row2.createCell(1).setCellValue("21");
            row2.createCell(2).setCellValue("20170101");
            row2.createCell(3).setCellValue(78);
        }
        //设置标题,以及设置单元格的样式,有些样式只对单元格有效
        Row row5 = sheet.createRow(0);
        Cell cell1 = row5.createCell(0);
        cell1.setCellValue("问卷调查详细信息");
        cell1.setCellStyle(style);
        //合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        //row5.createCell(3).setCellValue();

        //输出Excel对象
        FileOutputStream output = new FileOutputStream(path);
        hssfWorkbook.write(output);
        output.flush();
    }

    public void createExcelTop2(String path, List<SurveyItem> surveyItemList, List<Map> mapList) throws Exception {
        //创建Excel对象
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //Excel样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //Excel字体设置
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        hssfFont.setColor(HSSFColor.RED.index);
        style.setFont(hssfFont);
        //创建sheet对象
        Sheet sheet = hssfWorkbook.createSheet();
        //创建row
        Row row = sheet.createRow(1);
        //创建cell
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("用户名");
        row.createCell(2).setCellValue("机构信息");
        for (int j = 0; j < surveyItemList.size() - 1; j++) {
            String an = surveyItemList.get(j).getTitle();
            if (StringUtils.isBlank(an)) {
                an = "";
            }
            row.createCell(j + 3).setCellValue(an);
        }

        //Excel设置单元格填充色
        HSSFCellStyle style2 = hssfWorkbook.createCellStyle();
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
        style2.setFillForegroundColor(HSSFColor.RED.index);//前景填充色

//        for (int i = 2; i < 4; i++) {
//            Row row2 = sheet.createRow(i);
//            row2.createCell(0).setCellValue("李明");
//            Cell cell2 = row2.createCell(1);
//            cell2.setCellValue("21");
//            cell2.setCellStyle(style2);
//            row2.createCell(2).setCellValue("20170101");
//            row2.createCell(3).setCellValue(78);
//        }
        int vv = 0;
        for (int i = 2; i < mapList.size() + 2; i++) {
            Row row2 = sheet.createRow(i);
            Map ooo = mapList.get(vv);
            row2.createCell(0).setCellValue(ooo.get("xuhao").toString());
            row2.createCell(1).setCellValue(ooo.get("username").toString());
            row2.createCell(2).setCellValue(ooo.get("organname").toString());
            int bbbbv = 3;
            for (SurveyItem surveyItem : surveyItemList) {
                Object nn = ooo.get(surveyItem.getId());
                if (nn == null) {
                    row2.createCell(bbbbv).setCellValue("");
                } else {
                    row2.createCell(bbbbv).setCellValue(nn.toString());
                }
                bbbbv++;
            }
            vv++;
        }
        //设置标题,以及设置单元格的样式,有些样式只对单元格有效
        Row row5 = sheet.createRow(0);
        Cell cell1 = row5.createCell(0);
        cell1.setCellValue("问卷调查详细信息");
        cell1.setCellStyle(style);
        //合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        //row5.createCell(3).setCellValue();

        //输出Excel对象
        FileOutputStream output = new FileOutputStream(path);
        hssfWorkbook.write(output);
        output.flush();
    }
}
发布者:songJian   点击数:112   发布时间:2020-02-28 14:00:11   更新时间:2020-02-28 14:00:11
正在加载评论...
相关文章