电力项目 poi\jxl报表学习总结

最新推荐文章于 2021-08-31 17:52:11 发布

小葫芦西瓜

最新推荐文章于 2021-08-31 17:52:11 发布

阅读量402

点赞数

分类专栏：笔记文章标签： poi jxl

本文链接：https://blog.csdn.net/sinat_37164467/article/details/62233740

版权

笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

导出

1.导入poi使用的jar包

2.导入java文件 ExcelFileGenerator

/**
 * 系统数据导出Excel 生成器
 * @version 1.0
 */
package cn.itcast.elec.util;

import java.io.OutputStream;
import java.util.ArrayList;

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.hssf.util.HSSFColor;

public class ExcelFileGenerator {

	private final int SPLIT_COUNT = 1500; //Excel每个工作簿的行数

	private ArrayList fieldName = null; //excel标题数据集

	private ArrayList fieldData = null; //excel数据内容	

	private HSSFWorkbook workBook = null;

	/**
	 * 构造器
	 * @param fieldName 结果集的字段名
	 * @param data
	 */
	public ExcelFileGenerator(ArrayList fieldName, ArrayList fieldData) {

		this.fieldName = fieldName;
		this.fieldData = fieldData;
	}

	/**
	 * 创建HSSFWorkbook对象
	 * @return HSSFWorkbook
	 */
	public HSSFWorkbook createWorkbook() {

		workBook = new HSSFWorkbook();
		int rows = fieldData.size();
		int sheetNum = 0;

		if (rows % SPLIT_COUNT == 0) {
			sheetNum = rows / SPLIT_COUNT;
		} else {
			sheetNum = rows / SPLIT_COUNT + 1;
		}

		for (int i = 1; i <= sheetNum; i++) {
			HSSFSheet sheet = workBook.createSheet("Page " + i);
			HSSFRow headRow = sheet.createRow((short) 0); 
			for (int j = 0; j < fieldName.size(); j++) {
				HSSFCell cell = headRow.createCell((short) j);
				//添加样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				//添加样式
				//设置所有单元格的宽度
				sheet.setColumnWidth((short)j, (short)6000);
				//创建样式(使用工作本的对象创建)
				HSSFCellStyle cellStyle = workBook.createCellStyle();
				//创建字体的对象
				HSSFFont font = workBook.createFont();
				//将字体加粗
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				//设置字体的颜色
				short color = HSSFColor.RED.index;
				font.setColor(color);
				//将新设置的字体属性放置到样式中
				cellStyle.setFont(font);
				if(fieldName.get(j) != null){
					cell.setCellStyle(cellStyle);
					cell.setCellValue((String) fieldName.get(j));
				}else{
					cell.setCellStyle(cellStyle);
					cell.setCellValue("-");
				}
			}

			for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
				HSSFRow row = sheet.createRow((short) (k + 1));
				//将数据内容放入excel单元格
				ArrayList rowList = (ArrayList) fieldData.get((i - 1)
						* SPLIT_COUNT + k);
				for (int n = 0; n < rowList.size(); n++) {
					HSSFCell cell = row.createCell((short) n);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					if(rowList.get(n) != null){
						cell.setCellValue((String) rowList.get(n).toString());
					}else{
						cell.setCellValue("");
					}
				}
			}
		}
		return workBook;
	}

	public void expordExcel(OutputStream os) throws Exception {
		workBook = createWorkbook();
		workBook.write(os);
		os.close();
	}

}

3.jsp中添加按钮并增加js方法

function exportExcel(){
    var userName = document.getElementById("userName").value;
    var path = 'system/elecUserAction_export.do?userName='+userName;
  	openWindow(path,'700','400');
  }

4.在struts配置文件中添加跳转

5.在Action方法中添加

public String export(){
			是否在职)
		ArrayList filedName = elecUserService.getExcelFiledNameList(); 
		/**获取数据,(zhugeliang	诸葛亮	男	88886666	是
	 	            liubei	    刘备  	男	12345678	是
	                )
	       将zhugeliang	诸葛亮	男	88886666	是值存放到ArrayList dataList集合中
	       再实例化一个ArrayList filedData集合 filedData.add(dataList);
        */
		ArrayList filedData = elecUserService.getExcelFiledDataList(elecUserForm);
		try {
			//获取输出流
			OutputStream out = response.getOutputStream();
			//重置输出流
			response.reset();
			//设置导出Excel报表的导出形式
			response.setContentType("application/vnd.ms-excel");
			ExcelFileGenerator generator = new ExcelFileGenerator(filedName,filedData);
			generator.expordExcel(out);
			//设置输出形式
			System.setOut(new PrintStream(out));
			//刷新输出流
			out.flush();
			//关闭输出流
			if(out!=null){
				out.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

7.在serviceImpl中添加方法

public ArrayList getExcelFiledNameList() {
		String [] titles = {"登录名","用户姓名","性别","联系电话","是否在职"};
		ArrayList filedName = new ArrayList();
		for(int i=0;i<titles.length;i++){
			String title = titles[i];
			filedName.add(title);
		}
		return filedName;
	}

public ArrayList getExcelFiledDataList(ElecUserForm elecUserForm) {
		//组织查询条件
		String hqlWhere = "";
		List<String> paramsList = new ArrayList<String>();
		if(elecUserForm!=null && elecUserForm.getUserName()!=null && !elecUserForm.getUserName().equals("")){
			hqlWhere += " and o.userName like ?";
			paramsList.add("%" + elecUserForm.getUserName() + "%");
		}
		Object [] params = paramsList.toArray();
		//组织排序
		LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
		orderby.put("o.onDutyDate", "desc");
		List<ElecUser> list = elecUserDao.findCollectionByConditionNoPage(hqlWhere, params, orderby);
		List<ElecUserForm> formList = this.elecUserPOListToVOList(list);
		//构造报表导出数据
		ArrayList filedData = new ArrayList();
		for(int i=0;formList!=null && i<formList.size();i++){
			ArrayList dataList = new ArrayList();
			ElecUserForm userForm = formList.get(i);
			//zhugeliang	诸葛亮	男	88886666	是
			dataList.add(userForm.getLogonName());
			dataList.add(userForm.getUserName());
			dataList.add(userForm.getSexID());
			dataList.add(userForm.getContactTel());
			dataList.add(userForm.getIsDuty());
			filedData.add(dataList);
		}
		return filedData;
	}

jxl报表从Excel导入

1.导入jxl需要的jar包

2.导入java文件

/**
 * @author ly
 * Excel数据导入数据库
 * @version 1.0
 */
package cn.itcast.elec.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;



import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;

public class GenerateSqlFromExcel {

	
	/**
	 * 导入报表Excel数据，生成用户表的数据库导入语句
	 * @param formFile
	 * @return list ArrayList
	 * @throws Exception
	 */
	public static ArrayList generateStationBugSql(File formFile)
			throws Exception {
		InputStream in = null;
		Workbook wb = null;
		ArrayList list = new ArrayList();
		
		try {
			if (formFile == null) {
				throw new Exception("文件为空！");
			}

			in = new FileInputStream(formFile);
			
			wb = Workbook.getWorkbook(in);
			
			Sheet sheet[] = wb.getSheets();
			if (sheet != null) {
				for (int i = 0; i < sheet.length; i++) {
					if (!sheet[i].getName().equalsIgnoreCase("User")) {						
						throw new Exception("指定文件中不包含名称为User的sheet,请重新指定！");
					}
					for (int j = 1; j < sheet[i].getRows(); j++) {
						String[] valStr = new String[8];
						for (int k = 0; k < sheet[i].getColumns(); k++) {
							Cell cell = sheet[i].getCell(k, j);
							String content = "";
							if (cell.getType() == CellType.DATE) {
								DateCell dateCell = (DateCell) cell;
								java.util.Date importdate = dateCell.getDate();/**如果excel是日期格式的话需要减去8小时*/
								long eighthour = 8*60*60*1000;
								SimpleDateFormat simpledate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
								/**在当前日期上减8小时*/
								long time = importdate.getTime()-eighthour; 
								java.util.Date date = new java.util.Date();
								date.setTime(time);
								content = simpledate.format(date); 
							} else {
								String tempcontent = (cell.getContents() == null ? ""
										: cell.getContents());
								content = tempcontent.trim().replace('\'', ' ');
							}
							valStr[k] = content;
							
						} 
						list.add(j-1,valStr);
					}
				}
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (in != null) {
				try {
					in.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

3.需要待导入的excel文件模板

4.在jsp中添加导出按钮，并且跳转到新的导入页面

5.配置struts2文件

6.在vo对象中添加File属性

7.Action中添加方法

方法一负责跳转

方法二读取数据保存到数据库中

public String importdata(){
		elecUserService.saveElecUserWithExcel(elecUserForm);
		return "importdata";
	}

8.在serviceImpl中添加

@Transactional(isolation=Isolation.DEFAULT,propagation=Propagation.REQUIRED,readOnly=false)
	public void saveElecUserWithExcel(ElecUserForm elecUserForm) {
		try {
			File file = elecUserForm.getFile();
			GenerateSqlFromExcel generater = new GenerateSqlFromExcel();
			ArrayList<String[]> arrayList = generater.generateStationBugSql(file);
			MD5keyBean md5 = new MD5keyBean();
			for(int i=0;arrayList!=null && i<arrayList.size();i++){
				String[] data = arrayList.get(i);
				//实例化PO对象，用PO对象进行保存
				ElecUser elecUser = new ElecUser();
				//登录名	密码	用户姓名	性别	所属单位	联系地址	是否在职
				elecUser.setLogonName(data[0].toString());
				elecUser.setLogonPwd(md5.getkeyBeanofStr(data[1].toString()));
				elecUser.setUserName(data[2].toString());
				elecUser.setSexID(data[3].toString());
				elecUser.setJctID(data[4].toString());
				elecUser.setContactTel(data[5].toString());
				elecUser.setIsDuty(data[6].toString());
				elecUser.setBirthday(StringHelper.stringConvertDate(data[7].toString()));
				elecUserDao.save(elecUser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

另外，利用jxl导入的时候date会自动加8小时，需要自己减去8小时