2 kristenlee1218 kristenlee1218 于 2016.02.22 11:10 提问

急 在线等 谢谢!java想要读取excel中某以特定单元格的值,现在是循环读出所有的值,请帮忙修改一下

package com.excel.action;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JxlTable {

private final static JxlTable jxlTable = new JxlTable();

public static JxlTable getInstance() {
    return jxlTable;
}

public JxlTable() {
}

public boolean createTable(String[][] body, String filePath,int rowLength) {
    boolean createFlag = true;
    WritableWorkbook book;
    try {
        // 根据路径生成excel文件
        book = Workbook.createWorkbook(new File(filePath));
        // 创建一个sheet名为"表格"
        WritableSheet sheet = book.createSheet("表格", 0);
        // 设置NO列宽度
        sheet.setColumnView(1, 5);
        // 去掉整个sheet中的网格线
        sheet.getSettings().setShowGridLines(false);
        Label tempLabel = null;

        // 表体输出
        int bodyLen = body.length;


        String[][] bodyTempArr = body ;
        // 循环写入表体内容
        for(int i=0;i<rowLength;i++) {
              for(int j=0;j<body[i].length;j++) {
                WritableCellFormat tempCellFormat = null;
                tempCellFormat = getBodyCellStyle();
                if (tempCellFormat != null) {
                    if (j == 0 || j == (bodyTempArr.length - 1)) {
                        tempCellFormat.setAlignment(Alignment.CENTRE);
                    }
                }
                tempLabel = new Label(1 + j, 2 + i, bodyTempArr[i][j],
                        tempCellFormat);
                sheet.addCell(tempLabel);
            }
        }
        book.write();
        book.close();
    } catch (IOException e) {
        createFlag = false;
        System.out.println("EXCEL创建失败!");
        e.printStackTrace();
    } catch (RowsExceededException e) {
        createFlag = false;
        System.out.println("EXCEL单元设置创建失败!");
        e.printStackTrace();
    } catch (WriteException e) {
        createFlag = false;
        System.out.println("EXCEL写入失败!");
        e.printStackTrace();
    }

    return createFlag;
}

public WritableCellFormat getHeaderCellStyle() {
    WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10,
            WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
    WritableCellFormat headerFormat = new WritableCellFormat(
            NumberFormats.TEXT);
    try {
        // 添加字体设置
        headerFormat.setFont(font);
        // 设置单元格背景色:表头为黄色
        headerFormat.setBackground(Colour.YELLOW);
        // 设置表头表格边框样式
        // 整个表格线为粗线、黑色
        headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK,
                Colour.BLACK);
        // 表头内容水平居中显示
        headerFormat.setAlignment(Alignment.CENTRE);
    } catch (WriteException e) {
        System.out.println("表头单元格样式设置失败!");
    }
    return headerFormat;
}

public WritableCellFormat getBodyCellStyle() {
    WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10,
            WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
    WritableCellFormat bodyFormat = new WritableCellFormat(font);
    try {
        // 设置单元格背景色:表体为白色
        bodyFormat.setBackground(Colour.WHITE);
        // 设置表头表格边框样式
        // 整个表格线为细线、黑色
        bodyFormat
                .setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
    } catch (WriteException e) {
        System.out.println("表体单元格样式设置失败!");
    }
    return bodyFormat;
}

   /**
 * 读取Excel的内容,第一维数组存储的是一行中格列的值,二维数组存储的是多少个行
 * @param file 读取数据的源Excel
 * @param ignoreRows 读取数据忽略的行数,比喻行头不需要读入 忽略的行数为1
 * @return 读出的Excel中数据的内容
 * @throws FileNotFoundException
 * @throws IOException
 */
public static String[][] getData(File file, int ignoreRows)
       throws FileNotFoundException, IOException {
   List<String[]> result = new ArrayList<String[]>();
   int rowSize = 0;
   BufferedInputStream in = new BufferedInputStream(new FileInputStream(
          file));
   // 打开HSSFWorkbook
   POIFSFileSystem fs = new POIFSFileSystem(in);
   HSSFWorkbook wb = new HSSFWorkbook(fs);
   HSSFCell cell = null;
   for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
       HSSFSheet st = wb.getSheetAt(sheetIndex);
       // 第一行为标题,不取
       for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
          HSSFRow row = st.getRow(rowIndex);
          if (row == null) {
              continue;
          }
          int tempRowSize = row.getLastCellNum() + 1;
          if (tempRowSize > rowSize) {
              rowSize = tempRowSize;
          }
          String[] values = new String[rowSize];
          Arrays.fill(values, "");
          boolean hasValue = false;
          for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
              String value = "";
              cell = row.getCell(columnIndex);
              if (cell != null) {
                 // 注意:一定要设成这个,否则可能会出现乱码
                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                 switch (cell.getCellType()) {
                 case HSSFCell.CELL_TYPE_STRING:
                     value = cell.getStringCellValue();
                     break;
                 case HSSFCell.CELL_TYPE_NUMERIC:
                     if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd")
                                   .format(date);
                        } else {
                            value = "";
                        }
                     } else {
                        value = new DecimalFormat("0").format(cell
                               .getNumericCellValue());
                     }
                     break;
                 case HSSFCell.CELL_TYPE_FORMULA:
                     // 导入时如果为公式生成的数据则无值
                     if (!cell.getStringCellValue().equals("")) {
                        value = cell.getStringCellValue();
                     } else {
                        value = cell.getNumericCellValue() + "";
                     }
                     break;
                 case HSSFCell.CELL_TYPE_BLANK:
                     break;
                 case HSSFCell.CELL_TYPE_ERROR:
                     value = "";
                     break;
                 case HSSFCell.CELL_TYPE_BOOLEAN:
                     value = (cell.getBooleanCellValue() == true ? "Y"
                            : "N");
                     break;
                 default:
                     value = "";
                 }
              }
              if (columnIndex == 0 && value.trim().equals("")) {
                 break;
              }
              values[columnIndex] = rightTrim(value);
              hasValue = true;
          }

          if (hasValue) {
              result.add(values);
          }
       }
   }
   in.close();
   String[][] returnArray = new String[result.size()][rowSize];
   for (int i = 0; i < returnArray.length; i++) {
       returnArray[i] = (String[]) result.get(i);
   }
   return returnArray;
}

/**
 * 去掉字符串右边的空格
 * @param str 要处理的字符串
 * @return 处理后的字符串
 */
 public static String rightTrim(String str) {
   if (str == null) {
       return "";
   }
   int length = str.length();
   for (int i = length - 1; i >= 0; i--) {
       if (str.charAt(i) != 0x20) {
          break;
       }
       length--;
   }
   return str.substring(0, length);
}



public static void main(String[] args) throws Exception {
     File file = new File("F:/tomcat7/webapps/Excel/upload/3.xls");
       String[][] result = getData(file, 1);
       int rowLength = result.length;
       System.out.println("rowLength="+rowLength);
       for(int i=0;i<rowLength;i++) {
           for(int j=0;j<result[i].length;j++) {
              System.out.print(result[i][j]+"\t\t");
           }
           System.out.println();
       }
    String[][] body = result;
    String filePath = "F:/tomcat7/webapps/Excel/upload/68.xls";
    JxlTable testJxl = JxlTable.getInstance();
    boolean flag = testJxl.createTable(body, filePath,rowLength);
    if (flag) {
        System.out.println("表格创建成功!!");
    }
}

}

4个回答

xionglangs
xionglangs   Rxr 2016.02.22 11:37
已采纳
xionglangs
xionglangs jxl的jar包
8 个月之前 回复
caizhenying
caizhenying Sheet sheet = readwb.getSheet(0); sheet.getRows()和sheet.getColumn()这2个方法似乎api中找不到
8 个月之前 回复
xionglangs
xionglangs getCell(j, i);前面的j是列数,后面的i是行数。
接近 2 年之前 回复
kristenlee1218
kristenlee1218 比如我想读(3,3)这个单元格的值应该在代码里怎么改?求助
接近 2 年之前 回复
Royal_lr
Royal_lr   Ds   Rxr 2016.02.22 11:41

看那个单元格在哪一行,,哪一列就能读了

kristenlee1218
kristenlee1218 比如我想读(3,3)这个单元格的值应该在代码里怎么改?求助
接近 2 年之前 回复
rui888
rui888   Ds   Rxr 2016.02.22 11:51

你excel 都有行号列好的, 找到对应的单元格就可以了。

rui888
rui888 你的result 是excel 里面的数据吗?如果是 可以尝试 result[2][2]
接近 2 年之前 回复
kristenlee1218
kristenlee1218 比如我想读(3,3)这个单元格的值应该在代码里怎么改?求助
接近 2 年之前 回复
zydarbo
zydarbo   2016.02.22 15:11

把main方法中的两重循环都注释掉,然后System.out.print(result[i][j]+"\t\t");
想输出哪个,就把坐标都减一写进i和j

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!