java 读取excel获取真实行数
来源:互联网 发布:mac 安卓手机助手 编辑:程序博客网 时间:2024/05/19 03:46
java 读取excel获取真实行数
公司采用的是apache提供的包,通过XML文件的映射,把EXCEL表和我们的Model对应起来.本来是校验正确的,结果莫名其妙到后面就会报空指针异常.
问题的原因:在没有格式的前提下,getLastRowNum方法能够正确返回最后一行的位置;getPhysicalNumberOfRows方法能够正确返回物理的行数;
* 在有格式的前提下,这两个方法都是不合理的;
* 所以,在做导入excel的时候,建议想要正确获取行数,可以做一个人为的约定,比如约定导入文件第一列不允许为空,行数就按照第一列的有效行数来统计;这样就能正确获取到实际想要的行数;
更新版本, 因为发现有时候 存在了加了样式的边框,边框的属性默认成为了 公式属性,导致后面空指针,现已修复
修改版:
-
-
-
-
-
-
-
- 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+"我");
-
- 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 {
-
- 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);
-
- 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