java 读取excel获取真实行数

来源：互联网发布：恶搞老鼠软件编辑：程序博客网时间：2024/05/19 07:10

java 读取excel获取真实行数

公司采用的是apache提供的包,通过XML文件的映射,把EXCEL表和我们的Model对应起来.本来是校验正确的,结果莫名其妙到后面就会报空指针异常.
问题的原因:在没有格式的前提下，getLastRowNum方法能够正确返回最后一行的位置；getPhysicalNumberOfRows方法能够正确返回物理的行数；
* 在有格式的前提下，这两个方法都是不合理的；
* 所以，在做导入excel的时候，建议想要正确获取行数，可以做一个人为的约定，比如约定导入文件第一列不允许为空，行数就按照第一列的有效行数来统计；这样就能正确获取到实际想要的行数；

更新版本, 因为发现有时候存在了加了样式的边框,边框的属性默认成为了公式属性,导致后面空指针,现已修复

修改版:

 /**     * 用来得到真实行数     * @param sheet     * @param flag  需要写进数据库的列数用逗号隔开 比如  (Sheet sheet,int 2,int 3);随意个     * @return     *      */public 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来进行判断,可以较快的得到真实的行数 ,以至于后面的集合循环输出的话不会出现空指针异常

##利用POI在EXCEL表格中导入图片

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);    }}

0 0