java实现Excel导入导出

使用 APACHE POI 实现Excel导入导出

实现内容: 需要锁定列集合,自定义列宽度集合 或者 列宽度自适应,以及对小数点的处理等等.

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelExport {



	/**
	 * 增加需要固定列的下标集合,需要锁定列集合,自定义列宽度集合-(常用此方法)
	 */
	public static <E> HSSFWorkbook ExcelExportFunction(List<User> tempList01
			, String[] headRowN
			, String[] headRowC
			, String[] lockRow
			, Map<String,Integer> setWidthRow
			, String sheetName){
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		sheet.protectSheet("edit");
		List<Integer> lockNumber = new ArrayList<>();
		int value=-1;
		for (int i = 0; i < headRowC.length; i++) {
			try {
				//获取到列宽值
				value=setWidthRow.get(headRowC[i]);

			}catch (Exception e){
				value=-1;
			}
			//设置列宽
			if (value!=-1){
				sheet.setColumnWidth(i,(short)(140*value));
			}else {
				/*设置宽列自适应*/
				sheet.autoSizeColumn((short)i);//列宽自适应
				sheet.setColumnWidth(i,(int)(sheet.getColumnWidth(i)*1.25));//列留白25%
				if(sheet.getColumnWidth(i)<(short) 55*60)
					sheet.setColumnWidth(i,(short) (55*60));//设置最小列宽
				//sheet.setColumnWidth(i,(short) 22*140);
			}
			//设置需要锁定的列
			for (int j = 0; j < lockRow.length; j++) {
				//获取到需要锁定的列
				if (headRowC[i].equals(lockRow[j])){
					lockNumber.add(i);
				}
			}
		}
		HSSFCell cell;
		HSSFRow row;
		HSSFCellStyle contextstyle =workbook.createCellStyle();
		HSSFCellStyle setBorder9 = ExcelStyleUtil.getBorder9Styel(workbook);
		HSSFCellStyle setBorder8 = ExcelStyleUtil.getBorder8Styel(workbook);
		HSSFCellStyle setBorderLocked = ExcelStyleUtil.getBorderLockedStyel(workbook);
		HSSFDataFormat format = workbook.createDataFormat();

		int rowint = 0;
		//标题 第一行
		row = sheet.createRow((short) rowint);
		cell = row.createCell(0);
		cell.setCellValue(sheetName);
		rowint++;
		//列头 第二行
		row = sheet.createRow((short) (rowint));
		int cellcount = headRowN.length;

		//第三行存入列头数据
		for (int i = 0; i < cellcount; i++) {
			cell = row.createCell(i);
			cell.setCellValue(headRowN[i]);
			cell.setCellStyle(setBorderLocked);
		}
		rowint++;
		//数据 第四行开始
		for (int i = 0; i < tempList01.size(); i++) {
			row = sheet.createRow((short) (rowint + i));
			System.out.println("-----------------------------------");
			for(int j=0; j<headRowN.length; j++){
				String val = (String) tempList01.get(i).get(headRowC[j]);

				Boolean isNum = false;//data是否为数值型
				Boolean isInteger=false;//data是否为整数
				System.out.println("类型---> "+headRowC[j].getClass().getCanonicalName());
				if (!"java.lang.String".equals(headRowC[j].getClass().getCanonicalName())){
					isNum = true;
					isInteger=true;
				}else {
					isNum = false;
					isInteger=false;
				}

				makeCell(j, row, val, setBorder9,setBorder8,setBorderLocked,format,isNum,isInteger,lockNumber);
			}
		}

		if(tempList01.size()==0){
			row = sheet.createRow((short) (rowint));
			makeCell(0, row, "暂无数据需要录入", contextstyle,false,false);
		}
		//*******************excel文件创建结束******************//
		return workbook;
	}

	/**
	 * 增加需要固定列的下标集合,需要锁定列集合,列宽度自适应
	 */
	public static <E> HSSFWorkbook ExcelExportFunction(List<User> tempList01
			, String[] headRowN
			, String[] headRowC
			, String[] lockRow
			, String sheetName){
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		sheet.protectSheet("edit");
		List<Integer> lockNumber = new ArrayList<>();
		for (int i = 0; i < headRowC.length; i++) {
			sheet.autoSizeColumn((short)i);//列宽自适应
			sheet.setColumnWidth(i,(int)(sheet.getColumnWidth(i)*1.25));//列留白25%
			if(sheet.getColumnWidth(i)<(short) 55*60)
				sheet.setColumnWidth(i,(short) (55*60));//设置最小列宽
			//设置需要锁定的列
			for (int j = 0; j < lockRow.length; j++) {
				//获取到需要锁定的列
				if (headRowC[i].equals(lockRow[j])){
					lockNumber.add(i);
				}
			}
		}
		HSSFCell cell;
		HSSFRow row;
		HSSFCellStyle contextstyle =workbook.createCellStyle();
		HSSFCellStyle setBorder9 = ExcelStyleUtil.getBorder9Styel(workbook);
		HSSFCellStyle setBorder8 = ExcelStyleUtil.getBorder8Styel(workbook);
		HSSFCellStyle setBorderLocked = ExcelStyleUtil.getBorderLockedStyel(workbook);
		HSSFDataFormat format = workbook.createDataFormat();

		int rowint = 0;
		//标题 第一行
		row = sheet.createRow((short) rowint);
		cell = row.createCell(0);
		cell.setCellValue(sheetName);
		rowint++;
		//列头 第二行
		row = sheet.createRow((short) (rowint));
		int cellcount = headRowN.length;

		//第三行存入列头数据
		for (int i = 0; i < cellcount; i++) {
			cell = row.createCell(i);
			cell.setCellValue(headRowN[i]);
			cell.setCellStyle(setBorderLocked);
		}
		rowint++;
		//数据 第四行开始
		for (int i = 0; i < tempList01.size(); i++) {
			row = sheet.createRow((short) (rowint + i));
			for(int j=0; j<headRowN.length; j++){
				String val = (String) tempList01.get(i).get(headRowC[j]);
				Boolean isNum = false;//data是否为数值型
				Boolean isInteger=false;//data是否为整数
				System.out.println("类型---> "+headRowC[j].getClass().getCanonicalName());

				if (val != null || "".equals(val)) {
					//判断data是否为数值型
					isNum = val.matches("^(-?\\d+)(\\.\\d+)?$");
					//判断data是否为整数（小数部分是否为0）
					isInteger=val.matches("^[-\\+]?[\\d]*$");
					if(headRowC[j].matches("month|year|number")) {//正则匹配不加小数点的部分
						isNum = false;
						isInteger=false;
					}
				}

				makeCell(j, row, val, setBorder9,setBorder8,setBorderLocked,format,isNum,isInteger,lockNumber);
			}
		}

		if(tempList01.size()==0){
			row = sheet.createRow((short) (rowint));
			makeCell(0, row, "暂无数据需要录入", contextstyle,false,false);
		}
		//*******************excel文件创建结束******************//
		return workbook;
	}
	
	
	private static void makeCell(int colNo, HSSFRow row, String val, HSSFCellStyle contextstyle,
			Boolean isNum,Boolean isInteger) {
		HSSFCell cell;
		if (isNum) {
			if (isInteger) {
				System.out.println("数值");
				contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
				cell = row.createCell(colNo);
				cell.setCellStyle(contextstyle);
				cell.setCellValue(Double.parseDouble(val));
            }else{
            	System.out.println("小数");
            	contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));//保留两位小数点
        		cell = row.createCell(colNo);
        		cell.setCellStyle(contextstyle);
				cell.setCellValue(Double.parseDouble(val));
            }                   
        } else {
    		cell = row.createCell(colNo);
    		cell.setCellStyle(contextstyle);
    		cell.setCellValue(val);
		}	
	}

	/**
	 * 增加需要固定列得下标参数
	 * @return
	 */
	private static void makeCell(int colNo, HSSFRow row, String val, HSSFCellStyle setBorder9,HSSFCellStyle setBorderLocked,
								 Boolean isNum,Boolean isInteger,Integer startIndex,Integer endIndex) {
		HSSFCell cell;
		if (isNum) {
			if (isInteger) {
				System.out.println("数值");
				setBorder9.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
				cell = row.createCell(colNo);
				cell.setCellStyle(setBorder9);
				cell.setCellValue(Double.parseDouble(val));
			}else{
				System.out.println("小数");
				setBorder9.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));//保留两位小数点
				cell = row.createCell(colNo);
				cell.setCellStyle(setBorder9);
				cell.setCellValue(Double.parseDouble(val));
			}
		} else if(colNo>=startIndex&&colNo<=endIndex){
			cell = row.createCell(colNo);
			cell.setCellStyle(setBorderLocked);
			cell.setCellValue(val);
		}
		else{
			cell = row.createCell(colNo);
			cell.setCellStyle(setBorder9);
			cell.setCellValue(val);
		}
	}

	/**
	 * 增加需要固定列得下标参数
	 * @return
	 */
	private static <E> void makeCell(int colNo, HSSFRow row, String val, HSSFCellStyle setBorder9,HSSFCellStyle setBorder8, HSSFCellStyle setBorderLocked,HSSFDataFormat format,
									 Boolean isNum, Boolean isInteger, List<Integer> lockNumber) {


		HSSFCell cell;
		if (isNum) {
			if (isInteger) {
				setBorder9.setDataFormat(format.getFormat("0"));
				//setBorder9.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
				System.out.println("数值:"+colNo);
				cell = row.createCell(colNo);
				cell.setCellStyle(setBorder9);
				cell.setCellValue(Integer.parseInt(val));
			}else{
				System.out.println("小数:"+colNo);
				setBorder8.setDataFormat(format.getFormat("0.000"));//保留三位小数点
				//setBorder9.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
				cell = row.createCell(colNo);
				cell.setCellStyle(setBorder8);
				cell.setCellValue(Double.parseDouble(val));
			}
		} else if(lockNumber.contains(colNo)){	//colNo
			cell = row.createCell(colNo);
			cell.setCellStyle(setBorderLocked);
			cell.setCellValue(val);
		}
		else{
			cell = row.createCell(colNo);
			cell.setCellStyle(setBorder9);
			cell.setCellValue(val);
		}
	}
	
	/**
	 * @param excelFilePath 真是路径， excelName 文件名，headRow 表头注释 ，headRowC 表头字段名
	 * @return 保存javaBean的list集合
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })	
	public static List<Map> readFromExcel(String excelFilePath, String excelName,String[] headRow,String[] headRowC) throws Exception {
		List<Map> MapList = new ArrayList<Map>();
		Map<String, String> condition = new HashMap<String, String>();
		File file = new File(excelFilePath);  // 根据服务器上路径，获取文件
		String[] split = file.getName().split("\\.");  // .是特殊字符，需要转义！！！！！
        Workbook workbook = null;
        if ( "xls".equals(split[1])){ // 根据文件后缀（xls/xlsx）进行判断
            FileInputStream fis = new FileInputStream(file);   // 文件流对象 
            workbook = new HSSFWorkbook(fis);					// HSSF - xls
        }else if ("xlsx".equals(split[1])){
        	FileInputStream fis = new FileInputStream(file);   // 文件流对象
        	workbook = new XSSFWorkbook(fis);					// XSSF - xlsx
        }else {
            throw new IllegalAccessException("文件类型错误！"); 
        }
        Sheet sheet = workbook.getSheetAt(0);  // 解析表格的sheet页，这里只能获取第一个
        Row firstRow = sheet.getRow(0);  // 读取第一行数据
        Cell firstCell = firstRow.getCell(0); // 读取第一行的第一列数据
		if(!excelName.equals(firstCell.toString())){	// 检查标题是否对应
			throw new IllegalAccessException("模板非法！"); 
		}	
		Row titleRow = sheet.getRow(1);	// 获取表格的第二行数据即为标题行
		for (int i = titleRow.getFirstCellNum(); i < titleRow.getLastCellNum(); i++) {
			Cell titleCell = titleRow.getCell(i); 
			if(null == titleCell || !titleCell.toString().equals(headRow[i])) { // 标题行单元格与写死的表头名称对比
				throw new IllegalAccessException("模板表头非法！"); 
			}
		}	
		int firstRowIndex = sheet.getFirstRowNum()+2;   // 第一行是表名，第二行为表头
        int lastRowIndex = sheet.getLastRowNum();
		/*NumberFormat numberFormat = NumberFormat.getInstance();
		numberFormat.setGroupingUsed(false);*/
        for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   // 遍历行
        	Map map  = new HashMap();
        	Row row = sheet.getRow(rIndex);
            if (row != null) {
                for (int cIndex = row.getFirstCellNum(); cIndex < row.getLastCellNum(); cIndex++) {   // 遍历列
                    Cell cell = row.getCell(cIndex);
                    if (cell != null) {
						String value = cell.toString();
						int i = cell.getCellType();
						//0：数值  1：字符串  2：公式型  3：空值  4：布尔型  5：错误
						if (i == HSSFCell.CELL_TYPE_NUMERIC) {//数值类型
							DecimalFormat df = new DecimalFormat("#.######"); //格式化为六位小数
							value = df.format(cell.getNumericCellValue());
						}
						map.put(headRowC[cIndex], value); // 全部以字符串存入
                    }
                }
                MapList.add(map);
            }
        }
		return MapList;
	}
}

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;


/**
 * 创建excel表样式
 */
public class ExcelStyleUtil {

	public static HSSFCellStyle getBorderStyel(HSSFWorkbook workbook){
		HSSFCellStyle setBorder = workbook.createCellStyle();
		setBorder.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
		setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
		setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return setBorder;
	}

	public static HSSFCellStyle getBorder8Styel(HSSFWorkbook workbook){
		HSSFCellStyle setBorder8 = workbook.createCellStyle();
		setBorder8.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		setBorder8.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		setBorder8.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		setBorder8.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		setBorder8.setBorderRight(HSSFCellStyle.BORDER_THIN);
		setBorder8.setBorderTop(HSSFCellStyle.BORDER_THIN);
		setBorder8.setLocked(false);  //不锁定单元格
		return setBorder8;
	}

	public static HSSFCellStyle getBorder9Styel(HSSFWorkbook workbook){
		HSSFCellStyle setBorder9 = workbook.createCellStyle();
		setBorder9.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		setBorder9.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		setBorder9.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		setBorder9.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		setBorder9.setBorderRight(HSSFCellStyle.BORDER_THIN);
		setBorder9.setBorderTop(HSSFCellStyle.BORDER_THIN);
		setBorder9.setLocked(false);  //不锁定单元格
		return setBorder9;
	}

	public static HSSFCellStyle getBorderLockedStyel(HSSFWorkbook workbook){
		//		设置输出单元格需要锁定的样式
		HSSFCellStyle setBorderLocked = workbook.createCellStyle();
		setBorderLocked.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); //设置单元格背景颜色 
		setBorderLocked.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLocked.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		setBorderLocked.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		setBorderLocked.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		setBorderLocked.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		setBorderLocked.setBorderRight(HSSFCellStyle.BORDER_THIN);
		setBorderLocked.setBorderTop(HSSFCellStyle.BORDER_THIN);
		setBorderLocked.setLocked(true);  //锁定单元格
		return setBorderLocked;
	}

	public static HSSFFont getFontStyel(HSSFWorkbook workbook){
		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 22);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		return font;
	}
}

import java.io.File;
import java.net.URISyntaxException;
import java.net.URL;


public class StringDB2 {

	//window title
	public static final String WINDOW_TITLE_FILE_IMPORT = "选择导入数据文件(*.xls)";

	//path
	public static final String FILE_UPLOAD_PATH = "Upload/excel/";
	public static final String FILE_DOWNLOAD_PATH = "Download/";

	//(上传文件大小不可超过50M)
	public static final String MSG_FILE_UPLOAD_CONSTRAINT = "(上传文件大小不可超过50M!)";
	public static final String MSG_SELECT_IMPORT_DATAFILE = "选择导入的Excel文件:";

	//error message
	public static final String ERRMSG_IMPORT_FILE_FORMAT_ERROR = "导入文件格式错误，只能导入以xls为后缀的文件，请选择正确的文件导入!";
	public static final String ERRMSG_IMPORT_FILE_IS_EMPTY = "上传文件为空文件，请重新选择！";

	public static String getFullFilePath(String fileName) {
		String temp = "";
		try {
			temp = StringDB2.getWebRoot()+StringDB2.FILE_UPLOAD_PATH + fileName;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return temp;
	}

	public static String getWebRoot() throws IllegalAccessException{
		String path = getClassPath(StringDB2.class);
		//		getWebClassesPath();
		if (path.indexOf("WEB-INF") > 0) {
			if(isWindowsOS()){
				path = path.substring(0, path.indexOf("WEB-INF\\classes"));
			}else{
				path = path.substring(0, path.indexOf("WEB-INF/classes"));
			}
		} else {
			throw new IllegalAccessException("路径获取错误");
		}
		return path;
	}

	public static String getWebClassesPath() {
		String path = "";
		try {
			URL url = Thread.currentThread().getContextClassLoader()
					.getResource("");
			path = url.toURI().getPath();
		} catch (URISyntaxException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return path;
	}

	public static boolean isWindowsOS(){
		boolean isWindowsOS = false;
		String osName = System.getProperty("os.name");
		if(osName.toLowerCase().indexOf("windows")>-1){
			isWindowsOS = true;
		}
		return isWindowsOS;
	}

	public static File getClassFile(Class clazz){
		URL path = clazz.getResource(clazz.getName().substring(
				clazz.getName().lastIndexOf(".")+1)+".classs");
		if(path == null){
			String name = clazz.getName().replaceAll("[.]", "/");
			path = clazz.getResource("/"+name+".class");
		}
		return new File(path.getFile());
	}
	/**
	 * 得到当前类的路径
	 * @param clazz
	 * @return
	 */
	public static String getClassFilePath(Class clazz){
		try{
			return java.net.URLDecoder.decode(getClassFile(clazz).getAbsolutePath(),"UTF-8");
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return "";
		}
	}

	/**
	 * 取得当前类所在的ClassPath目录，比如tomcat下的classes路径
	 * @param clazz
	 * @return
	 */
	public static File getClassPathFile(Class clazz){
		File file = getClassFile(clazz);
		for(int i=0,count = clazz.getName().split("[.]").length; i<count; i++)
			file = file.getParentFile();
		if(file.getName().toUpperCase().endsWith(".JAR!")){
			file = file.getParentFile();
		}
		return file;
	}
	/**
	 * 取得当前类所在的ClassPath路径
	 * @param clazz
	 * @return
	 */
	public static String getClassPath(Class clazz){
		try{
			return java.net.URLDecoder.decode(getClassPathFile(clazz).getAbsolutePath(),"UTF-8");
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return "";
		}
	}

}

如有疑问留言即可!
欢迎大神们指点~