java io excel导入导出详解

来源：互联网发布：全球地名数据库编辑：程序博客网时间：2024/05/22 01:39

本章介绍excel导入导出后代代码，jsp页面导入参考下章文件上传下载，本章不作页面介绍.
本章基于spring框架。

jar包及源码下载：http://download.csdn.net/download/qq_22860341/9839553

excel导入

传入文件参数即可，或者通过File对象直接new地址。

package cy.component;import java.io.IOException;import java.io.InputStream;import org.apache.poi.poifs.filesystem.POIFSFileSystem;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;import org.springframework.web.multipart.MultipartFile;public class Import {    /*     * excel导入     */    public String importexcel(MultipartFile excelFile){        try {             InputStream inputStream = excelFile.getInputStream();             POIFSFileSystem fs = new POIFSFileSystem(inputStream);              Workbook workbook = WorkbookFactory.create(fs);               //获取sheet，若有多个用循环             Sheet sheet = workbook.getSheetAt(0);              //获取总行数             int rowCount = sheet.getPhysicalNumberOfRows();                //循环每一行             for (int i = 1; i <rowCount; i++){                //得到行对象                 Row row = sheet.getRow(i);                 //得到该行的第1列                 Cell cell0 = row.getCell(0);                  //得到该行的第2列                 Cell cell1 = row.getCell(1);                  //得到该行的第3列                 Cell cell2 = row.getCell(2);                  //知道多少列，有多少列加多少列                 //不知道多少列，获取每行有多少列，用循环                 //已经得到每行每列的数据，可以进行自己业务操作             }        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }          return null;    }}

excel导出

package cy.component;import java.io.IOException;import java.io.OutputStream;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.log4j.Logger;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.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.util.CellRangeAddress;public class Export {    /**     * 导出excel     */    private static Logger log = Logger.getLogger(Export.class);    /**     *      * @param response     * @param list  数据集合     * @param titles  列名,需要导出那些字段     * @param title   excel标题     */    public static void export(HttpServletResponse response,List<Object[]> list,String[] titles,String title){        //创建一个workbook，对应一个excel文件           HSSFWorkbook workBook = new HSSFWorkbook();         //添加一个sheet            HSSFSheet sheet = workBook.createSheet();             for(int i=0;i<titles.length;i++){                 sheet.setColumnWidth(i, 4000);             }            //第一行样式            HSSFFont font = workBook.createFont();            font.setFontHeightInPoints((short)16); //设置字体大小            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体            //设置单元格样式            HSSFCellStyle style = workBook.createCellStyle();            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中            //创建第一行并合并            HSSFRow row = sheet.createRow(0);            HSSFCell cell = row.createCell(0);            cell.setCellValue(title);      //主标题            cell.setCellStyle(style);            style.setFont(font);            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,titles.length-1 ));  //指定合并区域                //设置字体            style = workBook.createCellStyle();            style.setBorderBottom((short)2);            style.setBorderLeft((short)2);            style.setBorderRight((short)2);            style.setBorderTop((short)2);            font = workBook.createFont();            font.setFontHeightInPoints((short)16); //设置字体大小            style.setFont(font); //选择字体格式            //在sheet中添加表头第1行            row = sheet.createRow(1);            for(int i = 0; i < titles.length; i++){                String title1 = titles[i];                cell = row.createCell(i);                cell.setCellValue(title1);                cell.setCellStyle(style);            }            //写入信息            font = workBook.createFont();            font.setFontHeightInPoints((short)12); //设置字体大小            style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //居中            style.setFont(font); //选择字体格式            for(int i = 0; i < list.size(); i++){                Object[] obj = (Object[]) list.get(i);                row = sheet.createRow(i + 2);                for(int j=0;j<titles.length;j++){                    cell = row.createCell(j);                    if(obj[j]==null ||obj[j]==""){                        cell.setCellValue("");                    }else{                        cell.setCellValue(obj[j].toString());                    }                    cell.setCellStyle(style);                }            }            response.setContentType("application/vnd.ms-excel");              response.setHeader("Content-disposition", "attachment;filename=loco" +PubTools.getDate()+ ".xls");            try {                OutputStream ouputStream = response.getOutputStream();                workBook.write(ouputStream);                 ouputStream.flush();                  ouputStream.close();            } catch (IOException e) {                e.printStackTrace();                log.error(title+"导出失败：" + e.getMessage());            }      }}

0 0