Java导出及读取Excel工具类

1. 导入工具类包Hutool
2. 工具类代码

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @Description:    excel导出工具类
 * @Author:         Tara
 * @CreateDate:     2019/6/19 16:00
 * @Version:        1.0
 */
public class ExcelUtil {

    /**
     * @Description: 导出excel
     * @return:
     * @parm
     * @throw
     */
    public static void exportExcel(HttpServletResponse response,XSSFWorkbook workbook,String filename, String[] headers, List<Map<String,Object>> list) throws Exception {
        //清除buffer缓存
        response.reset();
        //数据处理
        List<List<String>>	data = new ArrayList<List<String>>();
        for (int i=0;i<list.size();i++){
            List<String> RowData = new ArrayList<String>();
            Map firstValue = list.get(i);
            Iterator<Map.Entry<String, String>>  iterator = firstValue.entrySet().iterator();
            while (iterator.hasNext()){
                Map.Entry<String,String> index = iterator.next();
                RowData.add(index.getValue() ==null?"":index.getValue());
            }
            data.add(RowData);
        }

        // 第一步，创建一个webbook，对应一个Excel以xsl为扩展名文件
        XSSFSheet sheet = workbook.createSheet();
        //设置列宽度大小
        sheet.setDefaultColumnWidth((short) 20);
        //第二步， 生成表格第一行的样式和字体
        XSSFCellStyle style = workbook.createCellStyle();
        // 标题样式
        //文本水平居中显示HorizontalAlignment
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 13);
        style.setFont(font);
        // 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell((short) i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 第三步：遍历集合数据，产生数据行，开始插入数据
        XSSFCellStyle fontStyle = workbook.createCellStyle();
        //文本水平居中显示HorizontalAlignment
        fontStyle.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //文本自动换行
        fontStyle.setWrapText(true);

        if (data != null) {
            int index = 1;
            for (List<String> m : data) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {
                    XSSFCell cell = row.createCell((int) cellIndex);
                    cell.setCellValue(str.toString());
                    cell.setCellStyle(fontStyle);
                    cellIndex++;
                }
                index++;
            }
        }
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("filename",filename);
        response.setHeader("Access-Control-Expose-Headers", "filename");
        OutputStream outStream = response.getOutputStream();
        workbook.write(outStream);
        outStream.flush();
        outStream.close();
    }

    /**
     * @Description: 读取excel内容
     * @return:  List
     * @parm File
     * @throw
     */
  public static  R readExcel(MultipartFile file,int startRowIndex){
        InputStream inputStream = null;
        try{
            inputStream = file.getInputStream();
        }catch (Exception e){
            e.printStackTrace();
            return R.error("文件读取失败");
        }
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);
        List<List<Object>> read = excelReader.read(startRowIndex-1);
        return R.ok().put("data",read);
    }
}

3.使用示例

//导出excel
@RequestMapping("/getExcel")
	public R getExcel(HttpServletResponse response,XSSFWorkbook workbook){
		List<Map<String,Object>> list = sysUserService.getUserInfo();
		if (list.size()>0){
			//文件名
			String filename = "text.xlsx";
			//表头
			String[] headers ={"姓名","性别","密码"};
			try {
				ExcelUtil.exportExcel(response,workbook,filename,headers,list);
				return R.ok();
			}catch (Exception e){
				return R.error("导出失败");
			}
		}
	}



//dao层
@Override
	public List<Map<String, Object>> getUserInfo() {
		return baseMapper.getUserInfo();
	}

//语句 为保证导出字段顺序， 返回值类型采用LinkedHashMap，有兴趣的可以自行百度hashmap与LinkedHashMap的区别
<select id="getUserInfo" resultType="java.util.LinkedHashMap">
	SELECT NAME,SEX,PASSWORD FROM SYS_USER_TEST
</select>

//导入excel
	@RequestMapping("/sys/login")
	public R test(@RequestParam MultipartFile file, @RequestParam int row){
		//row 指定开始读取数据行数，1表示第一行
		R r  = ExcelUtil.readExcel(file,row);
		if(("success").equals(r.get("msg"))){
			List<List<Object>> list =(List<List<Object>>) r.get("data");
			for (List<Object> objects : list) {
				//objects为每行数据
				System.out.println(objects);
				//objects为每行第一列数据
				System.out.println(objects.get(0).toString());
			}
			return R.ok();
		}else{
			return R.error("导入文件失败");
		}
	}