公司采用的是apache提供的包,通过XML文件的映射,把EXCEL表和我们的Model对应起来.本来是校验正确的,结果莫名其妙到后面就会报空指针异常.
问题的原因:在没有格式的前提下,getLastRowNum方法能够正确返回最后一行的位置;getPhysicalNumberOfRows方法能够正确返回物理的行数;
* 在有格式的前提下,这两个方法都是不合理的;
* 所以,在做导入excel的时候,建议想要正确获取行数,可以做一个人为的约定,比如约定导入文件第一列不允许为空,行数就按照第一列的有效行数来统计;这样就能正确获取到实际想要的行数;
更新版本, 因为发现有时候 存在了加了样式的边框,边框的属性默认成为了 公式属性,导致后面空指针,现已修复
修改版:
- /**
- * 用来得到真实行数
- * @param sheet
- * @param flag 需要写进数据库的列数用逗号隔开 比如 (Sheet sheet,int 2,int 3);随意个
- * @return
- *
- */
- ublic static int findRealRows(Sheet sheet, int... flag) {
- int row_real = 0;
- int rows = sheet.getPhysicalNumberOfRows();// 此处物理行数统计有错误,
- int size = flag.length;
- try {
- for (int i = 1; i < rows; i++) {
- Row row = sheet.getRow(i);
- int total = 0;
- ArrayList<Integer> blank =new ArrayList<Integer>();
- int type=-1;
- String s = null;
- for(int j:flag){
- if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){
- type=row.getCell(j).getCellType();
- row.getCell(j).setCellType(1);
- }
- if (row.getCell(j) == null||row.getCell(j).getStringCellValue().matches("^\\s+$")||row.getCell(j).getCellType()>2) {
- total++;
- if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){
- row.getCell(j).setCellType(type);
- }
- blank.add(j);
- }
- }
- System.out.println(s+"我");
- // 如果4列都是空说明就该返回
- if (total == flag.length) {
- return row_real;
- } else if (total == 0) {
- row_real++;
- } else {
- String h="";
- for(Integer b:blank){
- h=h+"第"+(b+1)+"列"+" ";
- }
- throw new BusinessException("第" + (i + 1) + "行" + h
- + "不能为空");
- }
- }
- } catch (NullPointerException e) {
- throw new BusinessException("excel格式异常,请检查excel格式有无数据缺失,无效数据行!");
- }
- return row_real;
- }
方法都这样,通过约定一个有的ID来进行判断,可以较快的得到真实的行数 ,以至于后面的集合循环输出的话不会出现空指针异常
- public void importExcelForCode(int x, int y, int width, int height,
- String no) throws Exception {
- //原装的EXCEL模板 如果要创建新的 在NWE XSSFWorkbook()中不需要带参数
- InputStream i = new FileInputStream("E:\\x.xlsx");
- //导入我的图片
- BufferedImage image = ImageIO.read(new File("E:\\name.jpg"));
- XSSFWorkbook xssfWorkbook = new XSSFWorkbook(i);
- XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
- XSSFDrawing xssfDrawing = sheet.createDrawingPatriarch();
- //字节流
- ByteArrayOutputStream bao = new ByteArrayOutputStream();
- ImageIO.write(image, "jpg", bao);
- //设置起始坐标,前四个是xy函数, 后四个是设置起始行列和图形行列 我选择后者
- XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, x, y, width
- + x, height + y);
- anchor.setAnchorType(0);
- //创建图片
- xssfDrawing.createPicture(anchor, xssfWorkbook.addPicture(
- bao.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
- //关闭流
- i.close();
- FileOutputStream fos = new FileOutputStream(new File(
- "E:\\x.xlsx"));
- xssfWorkbook.write(fos);
- new File("E:\\" + no + ".jpg").delete();
- }
##使用Zxing打造带有数字的二维码
- package com.x.ExportExcel;
- import java.awt.Color;
- import java.awt.Font;
- import java.awt.Graphics2D;
- import java.awt.RenderingHints;
- import java.awt.image.BufferedImage;
- import java.io.ByteArrayOutputStream;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.Hashtable;
- import javax.imageio.ImageIO;
- import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
- import org.apache.poi.xssf.usermodel.XSSFDrawing;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import com.google.zxing.BarcodeFormat;
- import com.google.zxing.EncodeHintType;
- import com.google.zxing.MultiFormatWriter;
- import com.google.zxing.client.j2se.MatrixToImageWriter;
- import com.google.zxing.common.BitMatrix;
- public class Code_128Utils {
- private static final int WIDTH = 351;
- private static final int CODEHEIGHT = 55;
- private static final int HEIGHT = 78;
- private static final int FONTSIZE = 25;
- private static final String IMAGETYPE = "JPEG";
- //该方法用来生成所需二维码 不带数字
- public static void createCode(String no) throws Exception {
- FileOutputStream fos;
- fos = new FileOutputStream(new File("E:\\code\\" + no + ".jpg"));
- int width = WIDTH;
- int height = CODEHEIGHT;
- Hashtable<EncodeHintType, String> hints = new Hashtable<EncodeHintType, String>();
- hints.put(EncodeHintType.CHARACTER_SET, "utf-8");
- BitMatrix m = new MultiFormatWriter().encode(no,
- BarcodeFormat.CODE_128, width, height, hints);
- MatrixToImageWriter.writeToStream(m, IMAGETYPE, fos);
- fos.flush();
- fos.close();
- createFont(no);
- }
- ///该方法用来生成二维码字体并且把二维码拼接到字体图片上
- public static void createFont(String no) throws Exception {
- BufferedImage font = new BufferedImage(WIDTH, HEIGHT,
- BufferedImage.TYPE_INT_RGB);
- BufferedImage code = ImageIO.read(new File("E:\\code\\" + no + ".jpg"));
- Graphics2D g = (Graphics2D) font.getGraphics();
- //长宽是总的 字体加二维码的
- g.clearRect(0, 0, WIDTH, HEIGHT);
- g.setColor(Color.WHITE);
- g.fillRect(0, 0, WIDTH, HEIGHT);
- //字体渲染
- g.setRenderingHint(RenderingHints.KEY_ANTIALIASING,
- RenderingHints.VALUE_ANTIALIAS_ON);
- g.setRenderingHint(RenderingHints.KEY_RENDERING,
- RenderingHints.VALUE_RENDER_QUALITY);
- //在图片上把字写好
- for (int i = 0; i < no.length(); i++) {
- g.setColor(Color.black);
- Font font_ = new Font("Consolas", 0, FONTSIZE);
- g.setFont(font_);
- g.drawString(no.charAt(i) + "", (FONTSIZE * 2 + WIDTH - no.length()
- * FONTSIZE)
- / 2 + (i - 1) * FONTSIZE, CODEHEIGHT + HEIGHT - CODEHEIGHT);
- }
- //然后把二维码加上去
- g.drawImage(code, 0, 0, null);
- g.dispose();
- //进行图片处理,防止出现模糊
- int[] rgb = new int[3];
- for (int i = 0; i < WIDTH; i++) {
- for (int j = CODEHEIGHT; j < HEIGHT; j++) {
- int pixel = font.getRGB(i, j);
- rgb[0] = (pixel & 0xff0000) >> 16;
- rgb[1] = (pixel & 0xff00) >> 8;
- rgb[2] = (pixel & 0xff);
- if (rgb[0] > 125 || rgb[1] > 125 || rgb[2] > 125) {
- font.setRGB(i, j, -1);
- }
- if (rgb[0] < 100 || rgb[1] < 100 || rgb[2] < 100) {
- font.setRGB(i, j, -16777216);
- }
- }
- }
- File outputfile = new File("E:\\code\\" + no + ".jpg");
- ImageIO.write(font, IMAGETYPE, outputfile);
- }
- }