本文目錄一覽：
java怎麼讀取excel數據
引入poi的jar包，大致如下：
讀取代碼如下，應該能看得明白吧
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil2007 {
/**讀取excel文件流的指定索引的sheet
* @param inputStream excel文件流
* @param sheetIndex 要讀取的sheet的索引
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFSheet readExcel(InputStream inputStream,int sheetIndex) throws FileNotFoundException, IOException
{
return readExcel(inputStream).getSheetAt(sheetIndex);
}
/**讀取excel文件的指定索引的sheet
* @param filePath excel文件路徑
* @param sheetIndex 要讀取的sheet的索引
* @return
* @throws IOException
* @throws FileNotFoundException
*/
public static XSSFSheet readExcel(String filePath,int sheetIndex) throws FileNotFoundException, IOException
{
return readExcel(filePath).getSheetAt(sheetIndex);
}
/**讀取excel文件的指定索引的sheet
* @param filePath excel文件路徑
* @param sheetName 要讀取的sheet的名稱
* @return
* @throws IOException
* @throws FileNotFoundException
*/
public static XSSFSheet readExcel(String filePath,String sheetName) throws FileNotFoundException, IOException
{
return readExcel(filePath).getSheet(sheetName);
}
/**讀取excel文件，返回XSSFWorkbook對象
* @param filePath excel文件路徑
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFWorkbook readExcel(String filePath) throws FileNotFoundException, IOException
{
XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(filePath));
return wb;
}
/**讀取excel文件流，返回XSSFWorkbook對象
* @param inputStream excel文件流
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFWorkbook readExcel(InputStream inputStream) throws FileNotFoundException, IOException
{
XSSFWorkbook wb=new XSSFWorkbook(inputStream);
return wb;
}
/***讀取excel中指定的單元格，並返回字符串形式的值
* 1.數字
* 2.字符
* 3.公式（返回的為公式內容，非單元格的值）
* 4.空
* @param st 要讀取的sheet對象
* @param rowIndex 行索引
* @param colIndex 列索引
* @param isDate 是否要取的是日期（是則返回yyyy-MM-dd格式的字符串）
* @return
*/
public static String getCellString(XSSFSheet st,int rowIndex,int colIndex,boolean isDate){
String s=””;
XSSFRow row=st.getRow(rowIndex);
if(row == null) return “”;
XSSFCell cell=row.getCell(colIndex);
if(cell == null) return “”;
if (cell.getCellType() == 0) {//數字
if(isDate)s=new SimpleDateFormat(“yyyy-MM-dd”).format(cell.getDateCellValue());
else s = trimPointO(String.valueOf(getStringValue(cell)).trim());
}else if (cell.getCellType() == 1){//字符（excel中的空格，不是全角，也不是半角，不知道是神馬,反正就是” “這個）
s=cell.getRichStringCellValue().getString().replaceAll(” “, ” “).trim();
// s=cell.getStringCellValue();//07API新增，好像跟上一句一致
}
else if (cell.getCellType() == 2){//公式
s=cell.getCellFormula();
}
else if (cell.getCellType() == 3){//空
s=””;
}
return s;
}
/**如果數字以 .0 結尾，則去掉.0
* @param s
* @return
*/
public static String trimPointO(String s) {
if (s.endsWith(“.0”))
return s.substring(0, s.length() – 2);
else
return s;
}
/**處理科學計數法和百分比模式的數字單元格
* @param cell
* @return
*/
public static String getStringValue(XSSFCell cell) {
String sValue = null;
short dataFormat = cell.getCellStyle().getDataFormat();
double d = cell.getNumericCellValue();
BigDecimal b = new BigDecimal(Double.toString(d));
//百分比樣式的
if (dataFormat == 0xa || dataFormat == 9) {
b=b.multiply(new BigDecimal(100));
//String temp=b.toPlainString();
DecimalFormat df=new DecimalFormat(“0.00”);//保留兩位小數的百分比格式
sValue = df.format(b) + “%”;
}else{
sValue = b.toPlainString();
}
return sValue;
}
}
《JAVA》編程中怎麼用SXSSFWorkbook對已存在的excel操作進行寫數據操作
XSSFWorkbook wb=new XSSFWorkbook(參數);中的參數是InputStream ，你直接XSSFWorkbook wb=new XSSFWorkbook(fs);就可以了。
第一步查詢數據–這一步讀者自行實現自己的數據查詢 ListPointInfo points = null;
points = this.dao.getAllCollect(userId);
final MapString, ListPointInfo pointMap = new HashMap();
for (final PointInfo pointInfo : points) {
final String pt = pointInfo.getPointType(); if (pointMap.containsKey(pt)) {final ListPointInfo subList = pointMap.get(pt);
subList.add(pointInfo);
} else {final ListPointInfo subList = new ArrayList();subList.add(pointInfo);
pointMap.put(pt, subList
第二步：生成工作簿
final SXSSFWorkbook wb = new SXSSFWorkbook();
// 對每一種類型生成一個sheet
for (final Map.EntryString, ListPointInfo entry : pointMap.entrySet()) {
final ListPointInfo pts = entry.getValue();
// 獲取每種類型的名字–作為sheet顯示名稱–如果不需要分sheet可忽略
String typeName = “”;
if (this.dao.getTypeByTypeCode(pts.get(0).getPointType()) != null) {
typeName = this.dao.getTypeByTypeCode(pts.get(0).getPointType()).getPointTypeName();
}
final Sheet sheet = wb.createSheet(typeName);
//生成用於插入圖片的容器–這個方法返回的類型在老api中不同
final Drawing patriarch = sheet.createDrawingPatriarch();
// 為sheet1生成第一行，用於放表頭信息
final Row row = sheet.createRow(0);
// 第一行的第一個單元格的值
Cell cell = row.createCell((short) 0);
cell.setCellValue(“詳細地址”);
cell = row.createCell((short) 1);
cell.setCellValue(“經度”);
cell = row.createCell((short) 2);
cell.setCellValue(“緯度”);
cell = row.createCell((short) 3);
for (int i = 0; i pts.size(); i++) {
final Row each = sheet.createRow(i + 1);
Cell infoCell = each.createCell((short) 0);
infoCell.setCellValue(pts.get(i).getAddrDetail());
infoCell = each.createCell((short) 1);
infoCell.setCellValue(pts.get(i).getX());
infoCell = each.createCell((short) 2);
infoCell.setCellValue(pts.get(i).getY());
infoCell = each.createCell((short) 3);
//查詢獲取圖片路徑信息–該步讀者自定義
PointPic pic = this.dao.getPicInfoByPointId(pts.get(i).getId());
try {
if (pic != null) {
for (int k = 0; k 6; k++) {//因為有六張圖片，所以循環6次
final short colNum = (short) (4+k);
infoCell = each.createCell(colNum);
BufferedImage img = null;
switch (k) {
case 0:
if (!StringUtils.isEmpty(pic.getPicOneAddr())) {
File imgFile = new File(pic.getPicOneAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
case 1:
if (!StringUtils.isEmpty(pic.getPicTwoAddr())) {
File imgFile = new File(pic.getPicTwoAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
case 2:
if (!StringUtils.isEmpty(pic.getPicThreeAddr())) {
File imgFile = new File(pic.getPicThreeAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
case 3:
if (!StringUtils.isEmpty(pic.getPicFourAddr())) {
File imgFile = new File(pic.getPicFourAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
case 4:
if (!StringUtils.isEmpty(pic.getPicFiveAddr())) {
File imgFile = new File(pic.getPicFiveAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
case 5:
if (!StringUtils.isEmpty(pic.getPicSixAddr())) {
File imgFile = new File(pic.getPicSixAddr());
img = ImageIO.read(imgFile);
imgFile = null;
}
break;
}
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(img, “jpg”, byteArrayOut);
img = null;
//設置每張圖片插入位置
final XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colNum,
i + 1, (short) (colNum + 1), i + 2);//參數為圖片插入在表格的坐標，可以自行查看api研究參數
anchor.setAnchorType(0);
// 插入圖片
patriarch.createPicture(anchor, wb.addPicture(
byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
byteArrayOut.close();
byteArrayOut = null;
}
pic = null;
}
} catch (final Exception e) {
e.printStackTrace();
}
}
}
final ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (final IOException e) {
e.printStackTrace();
}
final byte[] content = os.toByteArray();
final String url = Var.BASE_URL+ File.separator + “output.xls”;//讀者自定義路徑
final File file = new File(url);// Excel文件生成後存儲的位置。
OutputStream fos = null;
try {
fos = new FileOutputStream(file);
fos.write(content);
os.close();
fos.close();
} catch (final Exception e) {
e.printStackTrace();
}
return url;//文件保存成功
Java POI讀取Excel的時候怎麼按列讀取
按列讀取的方法：
String pathname = “E:\\files\\title.xlsx”;
File file = new File(pathname);
InputStream in = new FileInputStream(file);
//得到整個excel對象
XSSFWorkbook excel = new XSSFWorkbook(in);
//獲取整個excel有多少個sheet
int sheets = excel.getNumberOfSheets();
//便利第一個sheet
MapString,String colMap = new HashMapString, String();
for(int i = 0 ; i sheets ; i++ ){
XSSFSheet sheet = excel.getSheetAt(i);
if(sheet == null){
continue;
}
int mergedRegions = sheet.getNumMergedRegions();
XSSFRow row2 = sheet.getRow(0);
MapInteger,String category = new HashMapInteger, String();
for(int j = 0 ; j mergedRegions; j++ ){
CellRangeAddress rangeAddress = sheet.getMergedRegion(j);
int firstRow = rangeAddress.getFirstColumn();
int lastRow = rangeAddress.getLastColumn();
category.put(rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()+”-“+row2.getCell(firstRow).toString());
}
//便利每一行
for( int rowNum = 1 ; rowNum = sheet.getLastRowNum() ; rowNum++ ){
System.out.println();
XSSFRow row = sheet.getRow(rowNum);
if(row == null){
continue;
}
short lastCellNum = row.getLastCellNum();
String cate = “”;
Integer maxIndex = 0;
for( int col = row.getFirstCellNum() ; col lastCellNum ; col++ ){
XSSFCell cell = row.getCell(col);
if(cell == null ){
continue;
}
if(“”.equals(cell.toString())){
continue;
}
int columnIndex = cell.getColumnIndex();
String string = category.get(columnIndex);
if(string != null !string.equals(“”)){
String[] split = string.split(“-“);
cate = split[1];
maxIndex = Integer.parseInt(split[0]);
System.out.println(cate+”–“+cell.toString());
}else {
//如果當前便利的列編號小於等於合併單元格的結束,說明分類還是上面的分類名稱
if(columnIndex=maxIndex){
System.out.println(cate+”–“+cell.toString());
}else {
System.out.println(“分類未知”+”–“+cell.toString());
}
}
}
}
}
}
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/241437.html
微信掃一掃 
支付寶掃一掃 