生活不易、且行且珍惜。网站首页 程序人生

Java通过Poi的开发Excel导入导出和下载功能

发布时间：2018-12-11 11:33编辑：zj 阅读：4640文章分类：Java互动QQ群：170915747

最近有用到Excel的下载、导入、导出功能。提供一个Excel模板给用户下载，用户根据规范填写模板然后再导入Excel数据，保存到数据库，也可导出类表数据为Excel。因为有时候页面添加功太麻烦，就做成这样的Excel批量导入。

Excel的下载

这项目用的是spring+Struts2+mybatis。

需要的jar包

<!-- POI-EXCEL -->
<dependency>
  <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>
 
 <!-- POI-EXCEL 这个包使用下面的最新ExcelUtil-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.15</version>
    </dependency>

有两种方式

第一种、把Excel模板放在项目目录，提供下载。（这种比较方便）

Excel的路径：webapp/common/excelModule/downloadModel.xlsx。

前台代码：

<form action="" name="Form" id="myForm" method="" enctype="multipart/form-data">
    <div id="zhongxin">
    <table style="width:95%;" >
           <tr>
          <td style="padding-top: 20px;"><input type="file" id="excel"/></td>
      </tr>
      <tr>
         <td style="text-align: center;padding-top: 10px;">
        <a class="btn btn-mini btn-primary" id="submitBtn">导入</a>
        <a class="btn btn-mini btn-success" id="downLoadExcel">下载模版</a>
         </td>
       </tr>
      </table>
  </div>
  <div id="zhongxin2"class="center" style="display:none"><br/><img src="images/jzx.gif"/>
  <br/><h4 class="lighter block green"></h4></div> 
</form>

js代码：

$('#downLoadExcel').click(function(){
    location.href ='downLoadModel.action'; 
});

action代码：

    /**
     * 下载文件的文件名和流
     */
    private String fileName;
    private InputStream is;
     
   略：get set方法。
     
    /**
     * 下载模板
     * 
     * @throws Exception
     */
    public String downLoadModel() throws Exception {
 
        // 为InputStream的is流参数赋值
        is = ServletActionContext.getServletContext()
        .getResourceAsStream("/common/excelModule/downloadModel.xlsx");
        fileName = new String("题目导入模板.xlsx".getBytes(), "ISO8859-1");
 
        return "success";
    }

Struts2配置文件：

<action name="*Question" class="com.bayan.keke.action.QuestionAction"
    method="{1}">
    <result name="toList">/WEB-INF/jsp/question/questionList.jsp</result>
    <result name="success" type="stream">
        <param name="contentType">application/vnd.ms-excel</param>
        <param name="contentDisposition">attachment;fileName=${fileName}</param>
        <param name="inputName">is</param>
        <param name ="bufferSize">4096</param>  
    </result>
</action>

好了，一个简单的下载功能就完成了。

注意：Struts的配置文件中的文件名和流要和action的对应。

第二种、自定义生成Excel模板，提供下载。（ExcelUtil工具类代码在底下）

/**
     * 下载模板
     * 
     * @throws Exception
     */
    public void downExcel() throws Exception {
 
        try {
            String[] titles =
                new String[] {"比赛名称", "报名者姓名", "报名者手机号", 
                "报名者身份证号", "性别", "邮箱", "住址", "职业", "分数"};
            String[] cols =
                new String[] {"partyName", "userName", "userPhone",
                 "userIdCard", "sex", "email",
                    "address", "career", "score"};
          List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
         ExcelUtil.outPutExcelByMap(list, titles, cols, "题目信息模板",
          getRequest(), getResponse());
            renderNull();
        } catch (Exception e) {
            e.printStackTrace();
        }
        toList();
 
    }

Excel的导入

js代码：通过ajax，formdata格式提交文件

//导入Excel
        $("#importExcel").click(function(){
            $('#importQuestion').modal('show'); 
        });
         
        $("#submitBtn").click(function(){
            var fileName = $("#excel").val();
            var suffix = (fileName.substr(fileName.lastIndexOf("."))).toUpperCase();
             
            //验证文件后缀名
            if(".XLS" != suffix && ".XLSX" != suffix ){
                alert("文件格式只能是.XLS或者.XLSL");
                return false;
            }
            var formData = new FormData();
            var file = $("#excel")[0].files[0];
            if (file != "") {
                formData.append("excel", file);
            }
            　 $.ajax({
                    type : "POST",
                    url : "importExcel.action",
                    data : formData,
                    cache : false,
                    processData : false,
                    contentType : false,
                    dataType:'json',  
                    success : function(data) {
                        if (data.result == "success") {
                            alert("导入题目成功");
                             
                            $('#importQuestion').modal('hide');
                            getQuestionList();
                        }else if(data.result == "fileNull"){
                            alert("导入的文件为空");
                        }else if(data.result == "excelNull"){
                            alert("导入的Excel表格内容为空");
                        }else if(data.result == "maxLength"){
                            alert("导入的Excel表格内容超过了最大1000");
                        } else if(data.result == "fail"){
                            alert("导入数据库失败");
                        }else if(data.result=="error"){
                            alert(data.codeError);
                        }else{
                            alert("导入失败");
                        }
                         
                    }
                });
            //$("#myForm").submit();
         
        });

action代码：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

/**
     * 导入Excel
     * 
     * @throws Exception
     */
    @SuppressWarnings("unused")
    public void importExcel() throws Exception {
        printStartLog("方法开始update", LOGGER);
        // 将客户端的文件上传到服务端
        String desPath = ServletActionContext.getServletContext()
                .getRealPath("/upload/questionModel");
        File destFile = new File(excelFileName);
        // 上传到本地
        FileUtils.copyFile(excel, destFile);
 
        JSONObject json = new JSONObject();
        if (destFile == null) {
            printDebugLog("导入的文件为空", LOGGER);
            json.element("result", "fileNull");
            print(json);
            return;
        }
        // 解析excel
        List<String[]> excelList = ExcelUtil.getExcelData(excel, 1);
 
        if (excelList.size() > 1000) {
            printDebugLog("导入的Excel表格内容超过了最大1000", LOGGER);
            json.element("result", "maxLength");
            print(json);
            return;
        }
 
        String lengthMsg = "", formatMsg = "", emptyMsg = "";
        int n = 2;
        // 格式校验
        for (String[] data : excelList) {
            if (data.length > 17 || Tools.isEmpty(data)) {
                formatMsg += "第" + n + "行数据为空或者超出列数、";
            }
            if (data[0].length() > 20) {
                lengthMsg += "第" + n + "行题目编号、";
            }
            if (Tools.isEmpty(data[1])) {
                emptyMsg += "第" + n + "行题目标题、";
            } else if (data[1].length() > 1000) {
                lengthMsg += "第" + n + "行题目标题、";
            }
            if (Tools.isEmpty(data[3])) {
                emptyMsg += "第" + n + "行有无题目附件、";
            } else if (!"0".equals(data[3]) && !"1".equals(data[3])) {
                formatMsg += "第" + n + "行有无题目附件只能填0或1、";
            } else if ("1".equals(data[3]) && Tools.isEmpty(data[4])) {
                formatMsg += "第" + n + "题目附件名必填、";
            } else if ("0".equals(data[3]) && Tools.isNotEmpty(data[4])) {
                formatMsg += "第" + n + "题目附件名不必填、";
            }
            if (Tools.isNotEmpty(data[4]) && data[4].length() > 255) {
                lengthMsg += "第" + n + "行题目附件名、";
            }
            if (Tools.isNotEmpty(data[5]) && data[5].length() > 200) {
                lengthMsg += "第" + n + "行题目标签、";
            }
            if (Tools.isEmpty(data[6])) {
                emptyMsg += "第" + n + "行所属学科、";
            } else if (!getCodeList(KeConstant.QUESTION_SUBJECT_TYPE_OWNID)
                                           .contains(data[6])) {
                formatMsg += "第" + n + "行所属学科值不在范围内、";
            }
            if (Tools.isEmpty(data[7])) {
                emptyMsg += "第" + n + "行题型类别、";
            } else if (!getCodeList(KeConstant.QUESTION_TYPE_OWNID).contains(data[7])) {
                formatMsg += "第" + n + "行题型类别值不在范围内、";
            }
            
             
            System.out.println(data[0] + "---" + data[1] + "---" + data[2]+
             "---" + data[3] + "---" + data[4] + "---"
                + data[5] + "---" + data[6] + "---" + data[7]);
            n++;
        }
        if (!"".equals(lengthMsg) || !"".equals(formatMsg) || !"".equals(emptyMsg)) {
            json.element("result", "error");
            json.element("codeError", assembleErrorMsg(formatMsg,emptyMsg,lengthMsg));
            print(json);
            return;
        }
        // 存入数据库操作======================================//
        // 新增列表
        boolean result = save(excelList);
        if (result) {
            json.element("result", "success");
            print(json);
        } else {
            json.element("result", "fail");
            print(json);
        }
 
        // 请求结束log
        printEndLog("更新学生结束返回值:", json.toString(), LOGGER);
    }
 
    private Boolean save(List<String[]> excelList) throws Exception {
        List<KeQuestion> saveQuestionList = new ArrayList<KeQuestion>();
        Date now = new Date();
        for (String[] data : excelList) {
            KeQuestion saveQuestion = new KeQuestion();
            saveQuestion.setId(Tools.getUniqueId());
            saveQuestion.setTitle(data[1]);
            saveQuestion.setIndex(data[0]);
            saveQuestion.setContent(data[2]);
            saveQuestion.setHasAnnex(Integer.parseInt(data[3]) == 0?false:true);
            saveQuestion.setResourceUrl(data[4]);
            saveQuestion.setSpan(data[5]);
           ......
          
        }
        boolean result = true;
        result = questionService.insertQuestion(saveQuestionList);
        return result;
    }
    private String assembleErrorMsg(String formatMsg,String emptyMsg,String length) {
        String tabf = !"".equals(formatMsg)||!"".equals(emptyMsg)?"---": "";
     String promptMsg="长度超过规定范围。" + tabf : "";
        String tabs = !"".equals(emptyMsg) ? "---" : "";
        promptMsg += !"".equals(formatMsg) ? formatMsg.substring(0, formatMsg.length()
         - 1) + "格式错误。" + tabs : "";
        promptMsg += !"".equals(emptyMsg) ? emptyMsg.substring(0, emptyMsg.length() 
        - 1) + "为空，无法导入。" : "";
        return promptMsg;
    }

注意：Excel的验证根据自己的需求来判断，验证无误的在通过对象保存到数据库中。Excel单元格通通为文本格式，不然有问题

遇到过的问题：当Excel最后一列为空时，比如一空有10列，但最后一列为空时拿到了ExcelList长度为9，，折腾了半天无果，就改为了通过第一行标题来获取列长度，这样就没问题了。注意数据还是从第二行开始获取。还有一个问题就是，当填的值为0等数字时，取到则为0.0，设置了Excel的单元格为文本格式还是没用，需要设置为强文本格式（选择单元格点导航栏数据中的分列，然后下一步，下一步，选择文本，完成即可）。

然后还有就是一定要效验好数据，不能漏了各种会出现的情况，不然保存到数据库出问题就大条了。

Excel的导出

java代码：

 /**
     * 导出表格信息为Excel
     * 
     * @throws Exception
     */
    public void excelExport() throws Exception {
 
        try {
            String[] titles = new String[] {"比赛名称", "报名人姓名", 
                    "报名人手机号", "身份证号", "报名时间", "分数"};
            String[] cols =
                new String[] {"partyName", "userName", "userPhone",
                     "userIdCard", "creationTime","score"};
                     
            // 查询比赛信息
            ExaminationPartyInfo keywords = new ExaminationPartyInfo();
            keywords.setIsDeleted(Const.ISDELETED_FALSE).setType(Const.PARTYTYPE_BS);
            if (Tools.isNotEmpty(Jurisdiction.getMerchanismId())) {
                keywords.setMerchanismId(Jurisdiction.getMerchanismId());
            }
     List<ExaminationPartyInfo>partyList =examinationPartyInfoService.findList(keywords);
            // 查询报名信息
            ExaminationRegisterInfo condition = new ExaminationRegisterInfo();
            condition.setPartyIdArray(Tools.array2Long(StringUtil
                .listToString(partyList, "partyId")));
            List<ExaminationRegisterInfo> registerList =
                examinationRegisterInfoService.findList(condition);
 
            Map<String, Object> partyMap = Tools.list2Map(partyList, "partyId", "name");
            Map<String, Object> userNameMap = new HashMap<String, Object>(16);
            Map<String, Object> userPhoneMap = new HashMap<String, Object>(16);
           Map<String, Object> userIdCardMap = new HashMap<String, Object>(16);
            if (registerList.size() > 0) {
                List<ExaminationUserInfo> userInfoList =
                   examinationUserInfoService.findByIdAsGroup(Tools.array2Long(StringUtil
                        .listToString(registerList, "userId")));
                if (userInfoList.size() > 0) {
                    userInfoList.stream().forEach(e -> {
                        userNameMap.put(String.valueOf(e.getUserId()), e.getName());
                        userPhoneMap.put(String.valueOf(e.getUserId()), e.getPhone());
                        userIdCardMap.put(String.valueOf(e.getUserId()), e.getIdCard());
                    });
                }
                registerList.stream().forEach(e -> {
                    e.put("partyName", partyMap.get(e.getPartyId()));
                    e.put("userName", userNameMap.get(e.getUserId()));
                    e.put("userPhone", userPhoneMap.get(e.getUserId()));
                    e.put("userIdCard", userIdCardMap.get(e.getUserId()));
                });
                ExaminationGradeInfo examinationGradeInfo =
                    getBean(ExaminationGradeInfo.class, "", true);
                examinationGradeInfo.setRegisterIdArray(Tools.array2Long(StringUtil.listToString(
                    registerList, "registerId")));
                examinationGradeInfo.setIsDeleted(Const.ISDELETED_FALSE);
                Page<ExaminationGradeInfo> examinationGradeInfoPage =
                    examinationGradeInfoService.findListInPageWithKeywords(
                        getParaToInt("pageNumber"), getParaToInt("pageSize"), examinationGradeInfo,
                        getPara("sortName"), getPara("sortOrder"));
 
                Map<String, Object> registerInfoMap = Tools.list2Map(registerList, "registerId");
                List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
                examinationGradeInfoPage.getList().forEach(
                    e -> {
                        Map<String, Object> map = new HashMap<String, Object>(16);
                        ExaminationRegisterInfo registerInfo =
                            (ExaminationRegisterInfo)registerInfoMap.get(e.getRegisterId());
                        if (registerInfo != null) {
                            map.put("userName", registerInfo.get("userName"));
                            map.put("userPhone", registerInfo.get("userPhone"));
                            map.put("userIdCard", registerInfo.get("userIdCard"));
                            map.put("partyName", registerInfo.get("partyName"));
                            map.put("creationTime", registerInfo.getCreationTime());
                        }
                        map.put("score", e.getScore());
                        list.add(map);
                    });
 
                ExcelUtil.outPutExcelByMap(list, titles, cols, "报名信息" + Tools.getUniqueId(),
                    getRequest(), getResponse());
            }
            renderNull();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

ExcelUtil 工具类：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

 package com.egaosoft.util;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
 
/**
 * 使用poi报表导出工具类 把poi的一个调用接口抽出来，便于导出功能的管理
 */
 
public class ExcelUtil {
 
    /**
     * 导出list中map做载体的数据到excel 参数说明： list:存放了Map数据的集合 hdNames:表头列名 hds:对应表头的数据KEY xlsName:导出文件名
     */
    public static <T> boolean outPutExcelByMap(List<Map<String, Object>> list, String[] hdNames, String[] hds,
        String xlsName, HttpServletRequest request, HttpServletResponse response) throws Exception {
 
        Workbook wb = new HSSFWorkbook(); // 创建工作薄
        Sheet sheet = wb.createSheet(); // 创建工作表
        sheet.autoSizeColumn((short)0); // 自适应宽度
        // 写入表头---Excel的第一行数据
        Row nRow = sheet.createRow(0); // 创建行
        for (int i = 0; i < hdNames.length; i++) {
            Cell nCell = nRow.createCell(i); // 创建单元格
            nCell.setCellValue(hdNames[i]);
        }
 
        // 写入每一行数据---一条记录就是一行数据
        for (int i = 0; i < list.size(); i++) {
            for (int j = 0; j < hds.length; j++) {
                Object o = list.get(i).get(hds[j]); // 得到列的值
                data2Excel(sheet, o, i + 1, j); // 将值写入Excel
            }
        }
        setSizeColumn(sheet, hdNames.length);
        return downloadExcel(wb, xlsName, request, response);
    }
 
    /**
     * 传递一个Wookbook，给定文件名，以及request和response下载Excel文档
     * 
     * @throws IOException
     */
    @SuppressWarnings("all")
    private static boolean downloadExcel(Workbook wb, String xlsName, HttpServletRequest request,
        HttpServletResponse response) throws IOException {
        if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
            xlsName = java.net.URLEncoder.encode(xlsName, "utf-8") + ".xls";
        } else {
            xlsName = new String(xlsName.getBytes("utf-8"), "iso-8859-1") + ".xls";
        }
        OutputStream os = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsName);
 
        wb.write(os);
        return true;
    }
 
    /**
     * 将数据写到Excel中
     */
    private static void data2Excel(Sheet sheet, Object o, Integer r, Integer c) {
        // 通过获得sheet中的某一列，有得到，没有创建
        Row nRow = sheet.getRow(r);
        if (nRow == null) {
            nRow = sheet.createRow(r);
        }
        // nRow.setColumnWidth(r, arg1);
 
        Cell nCell = nRow.createCell(c);
 
        // 根据不同类型进行转化，如有其它类型没有考虑周全的，使用发现的时候添加
        char type = 'x';
        if (o instanceof Integer) {
            type = 1;
        } else if (o instanceof Double) {
            type = 2;
        } else if (o instanceof Float) {
            type = 3;
        } else if (o instanceof String) {
            type = 4;
        } else if (o instanceof Date) {
            type = 5;
        } else if (o instanceof Calendar) {
            type = 6;
        } else if (o instanceof Boolean) {
            type = 7;
        } else if (o == null) {
            type = 8;
        }
 
        switch (type) {
            case 1:
                nCell.setCellValue((Integer)o);
                break;
            case 2:
                nCell.setCellValue((Double)o);
                break;
            case 3:
                nCell.setCellValue((Float)o);
                break;
            case 4:
                nCell.setCellValue((String)o);
                break;
            case 5:
                nCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(o));
                break;
            case 6:
                nCell.setCellValue((Calendar)o);
                break;
            case 7:
                nCell.setCellValue((Boolean)o);
                break;
            case 8:
                nCell.setCellValue("");
                break;
            default:
                nCell.setCellValue(o + "");
                break;
        }
    }
 
    public static List<String[]> getExcelData(File file) {
        return getData(file, 0).get(0);// 选择sheet1
    }
 
    public static List<String[]> getExcelData(File file, int rowStart) {
        return getData(file, rowStart).get(0);// 选择sheet1
    }
 
    @SuppressWarnings("deprecation")
    public static List<List<String[]>> getData(File file, int rowStart) {
        Workbook workbook;
        List<List<String[]>> data = new ArrayList<List<String[]>>();
        try {
            workbook = WorkbookFactory.create(new FileInputStream(file));
            Sheet sheet = null;
            // 循环sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheet = workbook.getSheetAt(i);
                List<String[]> rows = new ArrayList<String[]>();
                int colsnum = 0;
                // 循环每一行
                for (int j = rowStart; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if (null != row) {
                        // 列数以excel标题为准
                        colsnum = sheet.getRow(0).getLastCellNum();
                        String[] cols = new String[colsnum];
                        // 循环每一个单元格，以一行为单位，组成一个数组
                        for (int k = 0; k < colsnum; k++) {
                            // 判断单元格是否为null，若为null，则置空
                            if (null != row.getCell(k)) {
                                int type = row.getCell(k).getCellType();
                                // 判断单元格数据是否为数字
                                if (type == HSSFCell.CELL_TYPE_NUMERIC) {
                                    // 判断该数字的计数方法是否为科学计数法，若是，则转化为普通计数法
                                    if (String.valueOf(row.getCell(k).getNumericCellValue()).matches(".*[E|e].*")) {
                                        DecimalFormat df = new DecimalFormat("#.#");
                                        // 指定最长的小数点位为10
                                        df.setMaximumFractionDigits(10);
                                        cols[k] = df.format(row.getCell(k).getNumericCellValue());
                                        // 判断该数字是否是日期,若是则转成字符串
                                    } else if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) {
                                        Date d = row.getCell(k).getDateCellValue();
                                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                        cols[k] = formater.format(d);
                                    } else {
                                        BigDecimal number =
                                            BigDecimal.valueOf(Double.valueOf(row.getCell(k).toString()));
                                        if (number.compareTo(number.setScale(0)) == 0) {
                                            cols[k] = number.setScale(0).toString();
                                            continue;
                                        }
                                        cols[k] = (row.getCell(k) + "").trim();
                                    }
                                } else {
                                    cols[k] = (row.getCell(k) + "").trim();
                                }
                            } else {
                                cols[k] = "";
                            }
                        }
                        // 去除全是空值得行
                        int num = 0;
                        for (String col : cols) {
                            if (Tools.isEmpty(col)) {
                                num++;
                            }
                        }
                        if (num != cols.length) {
                            // 以一行为单位，加入list
                            rows.add(cols);
                        }
 
                    }
                }
                // 返回所有数据，第一个list表示sheet，第二个list表示sheet内所有行数据，第三个string[]表示单元格数据
                data.add(rows);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return data;
    }
 
    @SuppressWarnings("deprecation")
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            sheet.autoSizeColumn((short)columnNum);
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
 
                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, 256 * columnWidth + 184);
        }
    }
 
}

SSM框架下导入导出

导出：

/**
 * 导出题目
 */
@RequestMapping(value="excelExport")
@ResponseBody
public ModelAndView excelExport(){
    logBefore(logger, Jurisdiction.getUsername() + "导出信息");
    ModelAndView mv = this.getModelAndView();
    PageData pd = this.getPageData();
    String keywords = pd.getString("keywords"); // 检索条件 关键词
    if (null != keywords && !"".equals(keywords)) {
        pd.put("keywords", keywords.trim());
    }
    try {
        PageData bPd = new PageData();
        bPd.put("tableName", "aicp_question");
        bPd.put("columnName", "type");
        Map<String, String> basicMap = Tools.list2Map(basicVariableService.listAll(bPd), "value", "title");
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List<String> titles = new ArrayList<String>();
        titles.add("题目");
        titles.add("类型");
        titles.add("答案");
        titles.add("解析");
        titles.add("选项");
        dataMap.put("titles", titles);
        List<PageData> listData = aicpQuestionService.listAll(pd);
        List<PageData> varList = new ArrayList<PageData>();
        listData.stream().forEach(e ->{
            PageData vpd = new PageData();
            vpd.put("var1", e.getString("question"));
            vpd.put("var2", basicMap.get(e.getString("type")));
            vpd.put("var3", e.getString("answer"));
            vpd.put("var4", e.getString("parsing"));
            vpd.put("var5", e.getString("option"));
            varList.add(vpd);
        });
        dataMap.put("varList", varList);
        ObjectExcelView erv = new ObjectExcelView();
        mv = new ModelAndView(erv, dataMap);
    } catch (Exception e) {
        logger.error(e.toString(), e);
    }
    return mv;
}

导入和下载模板：

@RequestMapping(value = "/downExcel")
public void downExcel(HttpServletResponse response) throws Exception {
    FileDownload.fileDownload(response, PathUtil.getClasspath() + Const.FILEPATHFILE + "2020102120125.xls",
            "2020102120125.xls");
}
 
@RequestMapping(value = "/readExcel")
public ModelAndView readExcel(@RequestParam(value = "excel", required = false) MultipartFile file, @RequestParam(value = "warehouseId", required = false)String warehouseId)
        throws Exception {
    logBefore(logger, Jurisdiction.getUsername() + "：从EXCEL导入题目。");
    ModelAndView mv = this.getModelAndView();
    if (!this.hasAddQX(this.menuId)) {
        logAfter(logger, Jurisdiction.getUsername() + "：无权从EXCEL导入题目");
    } else {
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            if (null != file && !file.isEmpty()) {
                String filePath = PathUtil.getClasspath() + Const.FILEPATHFILE;
                String fileName = FileUpload.fileUp(file, filePath, "userexcel");
                List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 2, 0, 0);
 
                String date = Tools.date2Str(new Date());
                List<PageData> saveList = new ArrayList<PageData>();
                for (PageData pd : listPd) {
                    PageData cd = new PageData();
                    cd.put("id", getUniqueId());
                    cd.put("warehouseId", warehouseId);
                    cd.put("question", pd.get("var0"));
                    cd.put("option", pd.get("var4"));
                    cd.put("answer", pd.get("var2"));
                    cd.put("parsing", pd.get("var3"));
                    if(pd.getString("var1").equals("单选")){
                        cd.put("type", "0");
                    }else if(pd.getString("var1").equals("多选")){
                        cd.put("type", "1");
                    }else if(pd.getString("var1").equals("填空")){
                        cd.put("type", "2");
                    }else if(pd.getString("var1").equals("判断")){
                        cd.put("type", "3");
                    }
                    cd.put("createTime", new Date());
                    cd.put("modifiedTime", new Date());
                    cd.put("isDeleted", "0");
                    saveList.add(cd);
                }
                mv.addObject("data", JSONObject.fromObject(aicpQuestionService.insertMultiple(saveList)));
            }
        } catch (Exception e) {
            e.printStackTrace();
            mv.addObject("data",
                    JSONObject.fromObject(Tools.fillMap(map, false, Const.TYPE_ALERT, "导入失败！请及时联系管理员解决问题，以免发生后续错误。")));
        }
 
    }
    mv.setViewName("save_result");
    return mv;
}

ObjectExcelRead工具类

package com.fh.util;
 
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
 
/**
 * 从EXCEL导入到数据库
 * 创建人：FH Q313596790
 * 创建时间：2014年12月23日
 * @version
 */
public class ObjectExcelRead {
 
   /**
    * @param filepath //文件路径
    * @param filename //文件名
    * @param startrow //开始行号
    * @param startcol //开始列号
    * @param sheetnum //sheet
    * @return list
    */
   @SuppressWarnings({ "deprecation", "resource" })
   public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
      List<Object> varList = new ArrayList<Object>();
 
      try {
         File target = new File(filepath, filename);
         FileInputStream fi = new FileInputStream(target);
         HSSFWorkbook wb = new HSSFWorkbook(fi);
         HSSFSheet sheet = wb.getSheetAt(sheetnum);                 //sheet 从0开始
         int rowNum = sheet.getLastRowNum() + 1;                //取得最后一行的行号
 
         for (int i = startrow; i < rowNum; i++) {              //行循环开始
             
            PageData varpd = new PageData();
            HSSFRow row = sheet.getRow(i);                       //行
            int cellNum = row.getLastCellNum();                //每行的最后一个单元格位置
 
            for (int j = startcol; j < cellNum; j++) {          //列循环开始
                
               HSSFCell cell = row.getCell(Short.parseShort(j + ""));
               String cellValue = null;
               if (null != cell) {
                  switch (cell.getCellType()) {              // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                  case 0:
                     cellValue = String.valueOf((int) cell.getNumericCellValue());
                     break;
                  case 1:
                     cellValue = cell.getStringCellValue();
                     break;
                  case 2:
                     cellValue = cell.getNumericCellValue() + "";
                     // cellValue = String.valueOf(cell.getDateCellValue());
                     break;
                  case 3:
                     cellValue = "";
                     break;
                  case 4:
                     cellValue = String.valueOf(cell.getBooleanCellValue());
                     break;
                  case 5:
                     cellValue = String.valueOf(cell.getErrorCellValue());
                     break;
                  }
               } else {
                  cellValue = "";
               }
                
               varpd.put("var"+j, cellValue);
                
            }
            varList.add(varpd);
         }
 
      } catch (Exception e) {
         System.out.println(e);
      }
       
      return varList;
   }
}

package com.fh.util;
 
import java.util.Date;
import java.util.List;
import java.util.Map;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
/**
* 导入到EXCEL
* 类名称：ObjectExcelView.java
* @author FH Q313596790
* @version 1.0
 */
@SuppressWarnings({"deprecation", "unchecked"})
public class ObjectExcelView extends AbstractExcelView{
 
   @Override
   protected void buildExcelDocument(Map<String, Object> model,
         HSSFWorkbook workbook, HttpServletRequest request,
         HttpServletResponse response) throws Exception {
      // TODO Auto-generated method stub
      Date date = new Date();
      String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
      HSSFSheet sheet;
      HSSFCell cell;
      response.setContentType("application/octet-stream");
      response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
      sheet = workbook.createSheet("sheet1");
       
      List<String> titles = (List<String>) model.get("titles");
      int len = titles.size();
      HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
      headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      HSSFFont headerFont = workbook.createFont();   //标题字体
      headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      headerFont.setFontHeightInPoints((short)11);
      headerStyle.setFont(headerFont);
      short width = 20,height=25*20;
      sheet.setDefaultColumnWidth(width);
      for(int i=0; i<len; i++){ //设置标题
         String title = titles.get(i);
         cell = getCell(sheet, 0, i);
         cell.setCellStyle(headerStyle);
         setText(cell,title);
      }
      sheet.getRow(0).setHeight(height);
       
      HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
      contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      List<PageData> varList = (List<PageData>) model.get("varList");
      int varCount = varList.size();
      for(int i=0; i<varCount; i++){
         PageData vpd = varList.get(i);
         for(int j=0;j<len;j++){
            String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
            cell = getCell(sheet, i+1, j);
            cell.setCellStyle(contentStyle);
            setText(cell,varstr);
         }
          
      }
       
   }
 
}