欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java io excel导入导出详解

程序员文章站 2024-02-24 13:40:07
...

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

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

excel导入

  1. 传入文件参数即可,或者通过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());
            }  
    }


}
相关标签: excel