Excel表格导入与导出

导入

1.添加依赖

<dependency>
  <groupId>net.sourceforge.jexcelapi</groupId>
  <artifactId>jxl</artifactId>
  <version>2.6.10</version>
</dependency>

2.Controller层开发

@Controller
public class FileController {
	@Autowired
	ItemsService itemsService;
	   
	@RequestMapping(value = "/importData")
	public WebResult importData(@RequestParam("file") MultipartFile file) throws Exception {
	     WebResult webResult = WebResultHelper.newResult();
	     try{
	         //1.获取上传excel文件的数据
	         Workbook wb = Workbook.getWorkbook(file.getInputStream());
	         Sheet sheet = wb.getSheet(0);
	         for (int i = 1; i < sheet.getRows(); i++) {
	             ItemsCustom itemsCustom = new ItemsCustom();
	             Cell itemIdCell = sheet.getCell(0, i);
	             Cell itemNameCell = sheet.getCell(1, i);
	             Cell itemPriceCell = sheet.getCell(2, i);
	             Cell itemDetailCell = sheet.getCell(3, i);
	             itemsCustom.setItemId(Integer.parseInt(itemIdCell.getContents()));
	             itemsCustom.setItemName(itemNameCell.getContents());
	             itemsCustom.setItemPrice(Long.parseLong(itemPriceCell.getContents()));
	             itemsCustom.setItemDetail(itemDetailCell.getContents());
	             //2.修改数据，如果存在就修改，如果不存在就新增
	             Items items = itemsService.getItemById(itemsCustom.getItemId());
	             if(items == null){
	                 itemsService.addItem(itemsCustom);
	             }else{
	                 itemsService.updateItems(itemsCustom.getId(), itemsCustom);
	             }
	         }
	     }catch(Exception e){
	         WebResultHelper.newErrorResult("存入数据失败");
	     }
	     return webResult;
	}
}

3.Service层开发

public interface ItemsService {
	//添加商品
	void addItem(ItemsCustom itemsCustom) throws Exception;
	//修改商品信息
	int  updateItems(Integer id, ItemsCustom itemsCustom) throws Exception;
}

@Service
public class ItemsServiceImpl implements ItemsService {
    //添加商品
    @Override
    public void addItem(ItemsCustom itemsCustom) throws Exception {
        itemsMapperCustom.addItem(itemsCustom);
    }

	//修改商品信息
    @Override
    public int updateItems(Integer id, ItemsCustom itemsCustom) throws Exception {
        itemsCustom.setId(id);
        return itemsMapperCustom.updateByPrimaryKey(itemsCustom);
    }
}

4.Dao层开发

public class ItemsCustom{
	private Integer itemId;

    private String itemName;

    private Long itemPrice;

    private String itemDetail;
	//getter和setter方法
}

public interface ItemsMapperCustom {
	//根据商品id查询商品信息
    Items getItemById(Integer itemId) throws Exception;
	//添加商品
    void addItem(ItemsCustom itemsCustom) throws Exception;
    //修改商品信息
    int updateByPrimaryKey(ItemsCustom record) throws Exception;
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.steven.ssm.mapper.ItemsMapperCustom" >
	<resultMap id="queryItems" type="Items">
		<id column="item_id" property="itemId"/>
		<result column="item_id" property="itemId"/>
		<result column="item_name" property="itemName"/>
		<result column="item_price" property="itemPrice"/>
		<result column="item_detail" property="itemDetail"/>
	</resultMap>

	<!-- 根据商品id查询商品信息 -->
    <select id="getItemById" parameterType="int" resultMap="queryItems">
        select * from items where item_id = #{itemId}
    </select>
    
	<!-- 添加商品 -->
	<insert id="addItem" parameterType="itemsCustom">
	    insert into items(item_id,item_name,item_price,item_detail) values(#{itemId},#{itemName},#{itemPrice},#{itemDetail})
	</insert>

	<!-- 修改商品信息 -->
    <update id="updateByPrimaryKey" parameterType="itemsCustom">
        update items
        set item_name = #{itemName,jdbcType=VARCHAR},
        item_price = #{itemPrice,jdbcType=DECIMAL},
        item_detail = #{itemDetail,jdbcType=VARCHAR}
        where item_id = #{itemId,jdbcType=INTEGER}
    </update>
</mapper>

5.测试(导入的excel文件格式为.xls)
导入前数据库数据

excel表格数据

导入后数据库数据

导出

1.添加依赖

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>

2.代码

@RequestMapping(value = "/exportData")
public ResponseEntity<byte[]> download(HttpServletRequest request) throws Exception {
    //创建一个excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建一个sheet并命名为商品
    HSSFSheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "商品");

    //写入各个字段的名称,这里写死.可以根据传入的参数写入
    HSSFRow row = sheet.createRow((short) 0);
    HSSFCell itemIdCell = row.createCell(0);
    itemIdCell.setCellValue("itemId");
    HSSFCell itemNameCell = row.createCell(1);
    itemNameCell.setCellValue("itemName");
    HSSFCell itemPriceCell = row.createCell(2);
    itemPriceCell.setCellValue("itemPrice");
    HSSFCell itemDetailCell = row.createCell(3);
    itemDetailCell.setCellValue("itemDetail");

    //获取数据库数据
    List<Items> itemsList = itemsService.getItemsList();
    int iRow = 1;
    //写入各条记录,每条记录对应Excel中的一行
    Iterator<Items> iterator = itemsList.iterator();
    while (iterator.hasNext()) {
        Items items = iterator.next();
        row = sheet.createRow((short) iRow);;
        itemIdCell = row.createCell(0);
        itemIdCell.setCellValue(items.getItemId());
        itemNameCell = row.createCell(1);
        itemNameCell.setCellValue(items.getItemName());
        itemPriceCell = row.createCell(2);
        itemPriceCell.setCellValue(items.getItemPrice());
        itemDetailCell = row.createCell(3);
        itemDetailCell.setCellValue(items.getItemDetail());
        iRow++;
    }

    //生产excel文件
    String path = "E:\\代码存档\\springMVC_demo\\src\\main\\resources\\file\\items.xls";
    FileOutputStream destFile = new FileOutputStream(path);
    workbook.write(destFile);

    //下载
    File file = new File(path);
    HttpHeaders headers = new HttpHeaders();
    String downloadFileName = new String("items.xls".getBytes("UTF-8"),"iso-8859-1");
    headers.setContentDispositionFormData("attachment", "items.xls");
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file),headers, HttpStatus.CREATED);
}

3.测试