java jxl 导出数据到excel

最新推荐文章于 2021-11-09 11:38:19 发布

hanchufeng2020

最新推荐文章于 2021-11-09 11:38:19 发布

阅读量429

点赞数

本文链接：https://blog.csdn.net/u010460625/article/details/109305692

版权

java工具专栏收录该内容

35 篇文章 1 订阅

订阅专栏

1. jsp界面导出

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.hanwei.cn.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>Excel导出</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%
    	//获取数据 通常我们是从数据库获取
    	String[] titles = {"学号","姓名","性别","年龄","班级"};
    	String[] valueKeys = {"no","name","sex","age","class"};
    	//数据列表
    	List<HashMap<String,Object>> dataList = new ArrayList<HashMap<String,Object>>();
    	//填充值
    	HashMap<String,Object> valueObj;
    	for(int i=1;i<=100;i++){
    		valueObj = new HashMap<String,Object>();
    		valueObj.put("no", i);
    		valueObj.put("name", "学员"+i);
    		String sex ="女";
    		if(i%2==0){
    			sex ="男";
    		}
    		valueObj.put("sex", sex);
    		valueObj.put("age", "22");
    		valueObj.put("class", "java vip 韩威工作室");
    		dataList.add(valueObj);
    	}
    	String sheetName ="json 精英学员";
    	
    	ExcelUtils.exportData(valueKeys, sheetName, titles, dataList, "D:\\韩威战队.xls");
    	out.print("导出完毕");    
     %>
     
  </body>
</html>

2. ExcelUtils 导出工具类

package com.hanwei.cn;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/***
 * excel 操作
 * @author json
 * @version v1.0
 *
 */
public class ExcelUtils {
	/***
	 * 导出excel
	 * @param titles 表头的列表
	 * @param li 实际数据
	 * @param valuekey 结果列表的key key -- value
	 *@param path 生成的路径
	 */
	public static void exportData(String[] valuekeys,String sheetName,String[] titles,List<HashMap<String,Object>> li,String path){
		
		try {
			//创建工作表
			WritableWorkbook wb;
			//通过文件路径创建file对象，通过file对象创建可写工作簿
			wb = Workbook.createWorkbook(new File(path));
			//创建工作簿对象
			 WritableSheet sheet  = wb.createSheet(sheetName, 0);
			//xu
			Label label = null;
			//循环生产标题
			for(int i=0;i<titles.length;i++){
				//label 第一个参数表示的第几列，第二个参数是第几列
				label = new Label(i,0,titles[i]);
				sheet.addCell(label);
			}
			//遍历插入数据
			Label contetLabel = null;
			HashMap<String,Object> valueOjb = null;
			for(int i=1;i<=li.size();i++){
				valueOjb = li.get(i-1);
				for(int j=0;j<valuekeys.length;j++){
					//j列 i行
					contetLabel = new Label(j,i,String.valueOf(valueOjb.get(valuekeys[j])));
					sheet.addCell(contetLabel);
				}
			}
			//写入
			wb.write();
			//关闭工作部
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
}

3. 读取excel数据

package com.hanwei.cn;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelTest {
	public static void main(String[] args) {
		//导入excel
//		WritableWorkbook  wb; 
//		try {
//			//创建文件
//			wb = Workbook.createWorkbook(new File("test.xls"));
//			//生产名为 “第一个工作部”
//			WritableSheet sheet = wb.createSheet("第一个工作部", 0);
//			//创建一个单元格，（0,0）表示单元格的第一个行第一列
//			Label label = new Label(0,0,"this is  test!");
//			//将定义好的单元格添加到工作表
//			sheet.addCell(label);
//			wb.write();
//			wb.close();
//		} catch (IOException e) {
//			e.printStackTrace();
//		} catch (RowsExceededException e) {
//			e.printStackTrace();
//		} catch (WriteException e) {
//			e.printStackTrace();
//		}	
		//读取文件 excel导出
		//获取到wb
		try {
			Workbook bookReader = Workbook.getWorkbook(new File("test1.xls"));
			//获取sheet= 
			Sheet  sheet = bookReader.getSheet(0); //第一个
			
			//获取第一个单元格的值
			Cell cell = sheet.getCell(0,0);
			String content = cell.getContents();
			bookReader.close();
			System.out.println(content);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

4. 读取excel时候test1.xls的位置