JAVA实现Excel的导入导出

近期项目中需要实现导入excel数据到数据库,导出数据库的数据生成excel表格,由于第一次使用,做一下笔记方便以后使用.

一、Excel数据的导入

1.首先引入jar包,由于创建的是Maven项目,因此直接在项目中的pom.xml中配置

<!-- jxl.jar实现excel文件的获取 -->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>`

你可以在该链接查找自己想要jar的版本

2.获取前台传过来的文件.解析获取excel中的数据,存储到数据库
1.service层代码

public Map<String,Integer> saveExcel(MultipartFile file,String path,HttpServletRequest request){
		log.info("excel文件的导入");
		//返回值显示文件导入结果
		Map<String, Integer> maps = new HashMap<>();
		Integer sum = 0; ///总条数
		Integer success = 0;//成功数
		Integer fail = 0;//失败数
		Integer flag = 1;//判断是否有有用的表
		Workbook workbook = null;
		try {
			if(file!=null){
				//错误信息
				List<ExcelError> errors = new ArrayList<>();
				//由于项目中使用的springMvc获取的文件类型为MultipartFile类型
				//Workbook.getWorkbook获取的是file类型,尝试强转获取的文件类型为File,运行时报错
				//此处使用项目中已经有的方法将获取的文件上传到服务器创建的文件中,在方法的最后判断文件是否存在,然后进行删除
				fileService.uploadFile(request, file, path);
				//通过当前类获取文件的绝对路径
				String path1 = this.getClass().getClassLoader().getResource("../../").getPath()+path;
				//文件的名字
			    String fileName = file.getOriginalFilename();
				//获取excel文件
				workbook = Workbook.getWorkbook(new File(path1+"/"+fileName));
				int oSheet=100;
				//获取表名所对应的sheet,workbook.getNumberOfSheets()获取excel中有几张表
				for(int i=0;i<workbook.getNumberOfSheets();i++){
					if(workbook.getSheet(i).getName().equals("缴费、退费信息表")){
						oSheet=i;
					}
				}
				//订单
				if(oSheet!=100){
					Sheet sheet3 = workbook.getSheet(oSheet);
					//订单信息list(每条数据添加进去,最后实现批量添加数据)
					List<Order> orders = new ArrayList<>();
					//循环行sheet2.getRows(),导入的excel信息是直接从第四行开始的
					for (int i = 3; i < sheet3.getRows(); i++) {
						boolean ssheet=true;
						//信息数
						sum++;
						//循环列,从0开始,本次导入的excel格式是从第二列开始的
						for (int j = 1; j < sheet3.getColumns(); j++) {
							//当前行列对应的单元格
							Cell cell = sheet3.getCell(j, i);
							//获取行列所对应的内容
							String contents = cell.getContents();
							//错误信息
							ExcelError error = new ExcelError();
							//数据校验(不通过则ssheet=false,添加错误信息到errors )
							注:校验过程中errors.add(error);
						//当前行数据校验通过,导入数据到对象
						if(ssheet){
							//满足条件的条数++
							success++;
							Order order = new Order();
							注:此处orders 数据添加...
							orders.add(order);
						}else{
							//数据校验未通过
							fail++;
						}
					}
					//批量添加订单数据
					if(orders.size()>0){
						orderDao.insertOrders(orders);
					}
				}
				//错误信息批量添加
				if(errors.size()>0){
					int n = errorDao.insertExcelError(errors);
				}
			//判断是否有有用的表
			if(oSheet==100){
				flag=0;
				}
			}
			if(file==null){
				//没有文件
				maps.put("文件", 0);
			}
		} catch (Exception e) {
			e.printStackTrace(); 
		}finally{
			workbook.close();
			File img = new File(this.getClass().getClassLoader().getResource("../../").getPath()+path+"/"+file.getOriginalFilename());
		    //判断文件是否存在,删除文件
		    if(img.exists()){
				img.delete(); 
			} 
		}
		maps.put("flag", flag);//1:有有用的表;0:无有用的表
		maps.put("sum", sum);//总数据条数
		maps.put("success", success);//能成功添加的数据条数
		maps.put("fail",fail);//失败的数据条数
		return maps;
	}

2.批量添加数据导入,sql代码(使用的是mybatis);如果批量添加的数据中有唯一索引的数据可以使用REPLACE INTO替换INSERT INTO(在添加过程中发现唯一索引重复数据会自动删除原来的数据再添加,相当于更新数据!)
< insert id=“insertOrders” parameterType=“java.util.List” > INSERT INTO order(ddname) VALUES < foreach collection =“orders” item=“order” separator =","> ( #{order.ddname} ) < /foreach> < /insert>
二、Excel的导出并实现下载
1.代码demo如下

@RequestMapping("/out")
public String outExcel(HttpServletResponse response,HttpServletRequest request){
		log.info("service===outPutExcel");
		
		// 创建工作表
        WritableWorkbook book = null;
        response.reset();
        response.setCharacterEncoding("UTF-8");// 设置字符集
        try {
        	String path = this.getClass().getClassLoader().getResource("../..").getPath();
        	File schoolFile = new File(path+"/pages");
        	// 假如文件不存在,则创建 
			if(!schoolFile.exists()){
				schoolFile.mkdirs();
			}
        	book = Workbook.createWorkbook(new File(path+"/excel/"+"信息表.xls"));
            WritableSheet sheet = book.createSheet("下载信息表1", 0);
            //内容设置同表一
            WritableSheet sheet1 = book.createSheet("下载信息表2", 1);
            //合并单元格
            sheet.mergeCells( 0 , 0 , 3 , 0 );
            //设置字体;  
            WritableFont font1 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);  
            WritableCellFormat cellFormat1 = new WritableCellFormat(font1);  
            //设置背景颜色;  
            /* cellFormat1.setBackground(Colour.BLUE_GREY);  */
            //设置边框;  
            /*cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);*/  
            //设置自动换行;  
            cellFormat1.setWrap(true);  
            //设置文字居中对齐方式;  
            cellFormat1.setAlignment(Alignment.CENTRE);  
            //设置垂直居中;  
            cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE); 
            //信息表title
            sheet.addCell(new jxl.write.Label(0, 0, "excel信息",cellFormat1));
            //设置单元格高
            sheet.setRowView( 0 , 400 );
            sheet.addCell(new jxl.write.Label(0, 1, "名字"));
            //设置单元格宽度
            sheet.setColumnView( 0 , 20 );
            sheet.addCell(new jxl.write.Label(1, 1, "身份证号"));
            sheet.setColumnView( 1 , 20 );
            sheet.addCell(new jxl.write.Label(2, 1, "年龄"));
            sheet.setColumnView( 2 , 30 );
            //追加数据
            for (int i = 0; i < 2; i++) {
            	//由于设置标题和title,列从0开始,行i+2;
            	sheet.addCell(new jxl.write.Label(0, i+2, "zk"));
            	//身份证等需要设置文本格式,否则下载点击后会无法再次显示原来数据
            	sheet.addCell(new jxl.write.Label(1, i+2, "612732201811113333"));
            	//设置单元格文本样式
                WritableCellFormat wcfF = new WritableCellFormat(NumberFormats.TEXT); 
                //定义一个列显示样式
                CellView ddbh = new CellView();
                ddbh.setFormat(wcfF);//把定义的单元格格式初始化进去
                ddbh.setSize(20*265);//设置列宽度（不设置的话是0，不会显示）
        	    sheet1.setColumnView(1, ddbh);//设置工作表中第n列的样式
            	sheet.addCell(new jxl.write.Label(2, i+2, "123"));
			}
            
            book.write();
            book.close();
        } catch (Exception e) {
            log.error("导出excel出现异常", e);
        } 
        try {
			 String filename = "信息表.xls";
			 String mimeType = request.getServletContext().getMimeType(filename);
			//指明这是一个下载的respond
			 response.setContentType(mimeType);
			 log.info("要下载的文件名:"+filename);
			 response.setHeader("Content-Disposition", 
						"attachment;filename="+URLEncoder.encode(filename, "UTF-8"));
			 String path = this.getClass().getClassLoader().getResource("../..").getPath()+"/excel/"+"信息表.xls";
	    	 log.info("要下载的文件路径:"+path);
	    	 File file = new File(path);
	    	 //如果文件不存在
	    	 if(!file.exists()){
	    	     return null;
	    	 }
	    	 FileInputStream in = new FileInputStream(file);
	    	 //创建输出流
	    	 OutputStream out = response.getOutputStream();
	    	 //缓存区
	    	 byte buffer[] = new byte[1024];
	    	 int length=0;
	    	 log.info("下载文件了...");
	    	 while ((length = in.read(buffer)) != -1) {  
				    out.write(buffer, 0, length);  
				    out.flush();
				} 
	    	 //关闭
	    	 out.close();
	    	 in.close();
	    	return null;
	    	
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}finally {
        	String path = this.getClass().getClassLoader().getResource("../..").getPath();
        	File img = new File(path+"/excel/"+"信息表.xls");
        	// 假如文件存在,则删除 
			if(img.exists()){
				img.delete(); 
			}
        }
	}

2.下载结果展示
1:下载过程

2:下载结果

二. Java利用POI实现导入导出Excel表格示例代码

介绍

Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html

实现

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelOperate {

  public static void main(String[] args) {
    // 创建Excel表格
    createExcel(getStudent());

    // 读取Excel表格
    List<Student> list = readExcel();
    System.out.println(list.toString());
  }

  /**
   * 初始化数据
   * 
   * @return 数据
   */
  private static List<Student> getStudent() {
    List<Student> list = new ArrayList<Student>();
    Student student1 = new Student("小明", 8, "二年级");
    Student student2 = new Student("小光", 9, "三年级");
    Student student3 = new Student("小花", 10, "四年级");
    list.add(student1);
    list.add(student2);
    list.add(student3);
    return list;
  }

  /**
   * 创建Excel
   * 
   * @param list
   *      数据
   */
  private static void createExcel(List<Student> list) {
    // 创建一个Excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 创建一个工作表
    HSSFSheet sheet = workbook.createSheet("学生表一");
    // 添加表头行
    HSSFRow hssfRow = sheet.createRow(0);
    // 设置单元格格式居中
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    // 添加表头内容
    HSSFCell headCell = hssfRow.createCell(0);
    headCell.setCellValue("姓名");
    headCell.setCellStyle(cellStyle);

    headCell = hssfRow.createCell(1);
    headCell.setCellValue("年龄");
    headCell.setCellStyle(cellStyle);

    headCell = hssfRow.createCell(2);
    headCell.setCellValue("年级");
    headCell.setCellStyle(cellStyle);

    // 添加数据内容
    for (int i = 0; i < list.size(); i++) {
      hssfRow = sheet.createRow((int) i + 1);
      Student student = list.get(i);

      // 创建单元格，并设置值
      HSSFCell cell = hssfRow.createCell(0);
      cell.setCellValue(student.getName());
      cell.setCellStyle(cellStyle);

      cell = hssfRow.createCell(1);
      cell.setCellValue(student.getAge());
      cell.setCellStyle(cellStyle);

      cell = hssfRow.createCell(2);
      cell.setCellValue(student.getGrade());
      cell.setCellStyle(cellStyle);
    }

    // 保存Excel文件
    try {
      OutputStream outputStream = new FileOutputStream("D:/students.xls");
      workbook.write(outputStream);
      outputStream.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * 读取Excel
   * 
   * @return 数据集合
   */
  private static List<Student> readExcel() {
    List<Student> list = new ArrayList<Student>();
    HSSFWorkbook workbook = null;

    try {
      // 读取Excel文件
      InputStream inputStream = new FileInputStream("D:/students.xls");
      workbook = new HSSFWorkbook(inputStream);
      inputStream.close();
    } catch (Exception e) {
      e.printStackTrace();
    }

    // 循环工作表
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
      HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
      if (hssfSheet == null) {
        continue;
      }
      // 循环行
      for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
        if (hssfRow == null) {
          continue;
        }

        // 将单元格中的内容存入集合
        Student student = new Student();

        HSSFCell cell = hssfRow.getCell(0);
        if (cell == null) {
          continue;
        }
        student.setName(cell.getStringCellValue());

        cell = hssfRow.getCell(1);
        if (cell == null) {
          continue;
        }
        student.setAge((int) cell.getNumericCellValue());

        cell = hssfRow.getCell(2);
        if (cell == null) {
          continue;
        }
        student.setGrade(cell.getStringCellValue());

        list.add(student);
      }
    }
    return list;
  }
}

附上Student类的代码

public class Student {

  private String name;
  private int age;
  private String grade;

  public Student() {
  }

  public Student(String name, int age, String grade) {
    super();
    this.name = name;
    this.age = age;
    this.grade = grade;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }

  public String getGrade() {
    return grade;
  }

  public void setGrade(String grade) {
    this.grade = grade;
  }

  @Override
  public String toString() {
    return "Student [name=" + name + ", age=" + age + ", grade=" + grade
        + "]";
  }
}

测试结果

导出的Excel表格

打印读取的Excel数据

[Student [name=小明, age=8, grade=二年级], Student [name=小光, age=9, grade=三年级], Student [name=小花, age=10, grade=四年级]]

第一次复写,肯定有理解不足或者错误的地方,请指正。