jxl读写excel文件（util）

灰太狼 2020-11-21 19:30 812阅读 0赞

**1.jxl maven坐标**

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

**2.util代码**

import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    import java.util.TreeMap;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.web.multipart.MultipartFile;
    
    import com.alibaba.fastjson.JSON;
    import com.inesv.digiccy.dto.InesvUserDto;
    
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.SheetSettings;
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.read.biff.BiffException;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    
    public class ExcelUtils {
    	//下载文件
    	public final static void getExcel(HttpServletResponse response, String filePath) throws IOException {
    		if (filePath != null && !"".equals(filePath)) {
    			File exportFile = new File(filePath);
    
    			String fileName = exportFile.getName();
    			response.setContentType("application/x-" + fileName.substring(fileName.lastIndexOf(".") + 1));
    			response.setCharacterEncoding("UTF-8");
    			response.addHeader("Content-Disposition", "attachment;filename=" + fileName);// excel文件名
    			OutputStream oos = response.getOutputStream();
    			FileInputStream fis = new FileInputStream(exportFile);
    			byte[] b = new byte[10240];
    			int len;
    			while ((len = fis.read(b)) != -1) {
    				oos.write(b, 0, len);
    				oos.flush();
    			}
    			fis.close();
    			oos.close();
    		}
    	}
    	
    	// 导出excel
    	public final static void export(HttpServletResponse response, Map<String, List<String>>... contacts) {
    		// 文件名
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    		String fileName = sdf.format(new Date()) + ".xls";
    
    		response.setContentType("application/x-excel");
    		response.setCharacterEncoding("UTF-8");
    		response.addHeader("Content-Disposition", "attachment;filename=" + fileName);// excel文件名
    
    		try {
    			// 1.创建excel文件
    			WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream());
    			// 居中
    			WritableCellFormat wf = new jxl.write.WritableCellFormat();
    			wf.setAlignment(Alignment.CENTRE);
    
    			for (int i = 0; i < contacts.length; i++) {
    				Map<String, List<String>> contact = contacts[i];
    				WritableSheet sheet = null;
    				SheetSettings settings = null;
    				// 2.创建sheet并设置冻结前两行
    				sheet = book.createSheet("data" + i, i);
    				settings = sheet.getSettings();
    				settings.setVerticalFreeze(1);
    				Set<String> keys = contact.keySet();
    				int col = 0;
    				for (String key : keys) {
    					List<String> values = contact.get(key);
    					int row = 0;
    					sheet.addCell(new Label(col, row, key, wf));
    					for (String value : values) {
    						row++;
    						sheet.addCell(new Label(col, row, value, wf));
    					}
    					col++;
    				}
    			}
    			response.flushBuffer();
    			// 6.写入excel并关闭
    			book.write();
    			book.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    	//获取重复或者空的列
    	public static Map<String, Object> getRepeatAndNull(MultipartFile excelFile, String colTitle) throws Exception{
    		Map<String, Object> map = new HashMap<String, Object>();
    		//根据文件获取workbook对象
    		Workbook book = Workbook.getWorkbook(excelFile.getInputStream());
    		//获取工作表
    		Sheet[] sheets = book.getSheets();
    		if(sheets != null && sheets.length > 0) {
    			Sheet shet = sheets[0];
    			int k = shet.getColumns();
    			int pCol = -1;
    			for(int i = 0; i < k; i ++) {
    				String title = shet.getCell(i, 0).getContents();
    				if(colTitle.equals(title)) {
    					pCol = i;
    					break;
    				}
    			}
    			if(pCol != -1) {
    				List<String> chongfu = new ArrayList<String>();
    				int nullCount = 0;
    				int rows = shet.getRows();
    				for(int i = 0; i < rows; i ++) {
    					for(int j = 0; j < rows; j ++) {
    						if(i != j) {
    							String con1 = shet.getCell(pCol, i).getContents();
    							String con2 = shet.getCell(pCol, j).getContents();
    							if(con1 == null || "".equals(con1)) {
    								nullCount ++;
    							}
    							if(con1 != null && !"".equals(con1) && con1.equals(con2)) {
    								chongfu.add(con1);
    							}
    						}
    					}
    				}
    				if(chongfu.size() == 0 && nullCount == 0) {
    					map.put("msg", "success");
    				}else {
    					map.put("msg", "fail");
    					map.put("nullCount", nullCount);
    					map.put("cfList", chongfu);
    				}
    			}
    		}else {
    			map.put("msg", "error");
    		}
    		return map;
    	}
    	//将excel解析成map 只解析第一张工作表
    	public static Map<String, List<String>> getExcelContent(MultipartFile excelFile) throws Exception{
    		Map<String, List<String>> content = new HashMap<String, List<String>>();
    		if(excelFile == null) {
    			return content;
    		}
    		//根据文件获取workbook对象
    		Workbook book = Workbook.getWorkbook(excelFile.getInputStream());
    		//获取工作表
    		Sheet[] sheets = book.getSheets();
    		if(sheets.length <= 0) {
    			return content;
    		}
    		Sheet shet = sheets[0];
    		int cols = shet.getColumns();
    		int rows = shet.getRows();
    		for(int c = 0; c < cols; c++) {
    			String title = "";
    			List<String> cs = new ArrayList<String>();
    			for(int r = 0; r < rows; r ++) {
    				if(r == 0) {
    					title = shet.getCell(c, r).getContents();
    				}else {
    					cs.add(shet.getCell(c, r).getContents());
    				}
    			}
    			content.put(title, cs);
    		}
    		return content;
    	}
    	
    }

*Ps：jxl插件可能不支持高版本的excel，解析excel还可以使用poi插件。。*