[原]java输出Excel

最新推荐文章于 2024-09-20 17:36:55 发布

原创最新推荐文章于 2024-09-20 17:36:55 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel #string

JAVA 同时被 2 个专栏收录

187 篇文章

订阅专栏

总结

5 篇文章

订阅专栏

本文介绍如何使用poi.jar库实现将数据批量写入Excel文件，并处理数据大小限制及字符编码问题。

poi.jar

int excelSize = 65535;
	int excelSell = 32767;
	@SuppressWarnings("deprecation")
	public HSSFWorkbook writeBook(Map<String, List<List<String>>> sheetDataMap) throws IOException{
		//workbook工作表的序号
		int workBookNumber = 0;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = null;
		HSSFRow row = null;
		
		List<String> keyList = this.getKeys(sheetDataMap);
		for(String key : keyList){
			List<List<String>> dataList =  sheetDataMap.get(key);
			int dateListSize = dataList.size();
			int count = (dateListSize % excelSize == 0)?(dateListSize / excelSize):(dateListSize / excelSize + 1);
			for(int num = 0; num < count; num++){
				sheet = workbook.createSheet();
				workbook.setSheetName(workBookNumber++, key + (num + 1),(short)1);
				int tempNum = num * excelSize;
				for(int i = tempNum, tempSize = ((num + 1) * excelSize < dateListSize)?((num + 1) * excelSize):(dateListSize); i < tempSize; i++){
					row = sheet.createRow(i - tempNum);
					short colNum = 0;
					for(short j = 0, size1 = (short)dataList.get(i).size(); j < size1; j++){
						String tempStr = dataList.get(i).get(j);
						if("null".equals(tempStr) || tempStr == null || tempStr.length() == 0){
							tempStr = " ";
						}
//						//一个Excel单元格不能超过32767个字符
						int cellLen = tempStr.length();
						int celldiv = cellLen / excelSell;
						if(cellLen % excelSell != 0){
							celldiv += 1;
						}
						for(short m = 0; m < celldiv; m++){
							String colStr = tempStr.substring(m * excelSell, (m + 1) * excelSell > cellLen? cellLen : (m + 1) * excelSell);
							colNum = (short)(colNum + m);
							try {
								row.createCell(colNum).setCellValue(Integer.parseInt(colStr));
							} catch (NumberFormatException e) {
								try {
									row.createCell(colNum).setCellValue(Float.parseFloat(colStr));
								} catch (NumberFormatException e1) {
									row.createCell(colNum).setCellValue(new HSSFRichTextString(colStr));
								}
							}
						}
						colNum++;
					}
				}
			}
		}
		return workbook;
	}

		HttpServletResponse response = ServletActionContext.getResponse();
		String name = fileName;
		try {
			name = URLEncoder.encode(fileName, "UTF-8");
		} catch (UnsupportedEncodingException e1) {
			name = fileName;
		}
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		response.setHeader("Content-Disposition", "attachment;filename=" + name);
		try {
			HSSFWorkbook workbook = excel.writeBook(excelData);
			workbook.write(response.getOutputStream());
			response.getOutputStream().flush();
			response.getOutputStream().close();
		} catch (IOException e) {
			//当浏览器关闭连接，此处response会报错
			System.out.println("Excel输出异常");
		}