版本二:加入了图片写出到表格,优化了版本一
package com.wenhao.three;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;/**
* POI工具类:
* 获取指定位置单元格的值
* 设置指定位置单元格的值
*/
public class MyPOIUtils {
public static Workbook workbook ;
public static Sheet sheet;
public static FileInputStream fileInputStream ;
public static FileOutputStream out = null;
/**
* 确认读取文件的版本类型
* @param filePath
*/
public static void insureExcelType(String filePath) {
try {
fileInputStream = new FileInputStream(filePath);
if (filePath.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fileInputStream);
// sheet = workbook.getSheetAt(0);
} else if (filePath.endsWith(".xls")) {
POIFSFileSystem fileSystem = new POIFSFileSystem(fileInputStream);
workbook = new HSSFWorkbook(fileSystem);
// sheet = workbook.getSheetAt(0);
} else {
throw new RuntimeException("错误提示: 您设置的Excel文件名不合法!");
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static void insureExcelType(String filePath,String fileName) {
try {
fileInputStream = new FileInputStream(filePath + fileName);
if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fileInputStream);
} else if (fileName.endsWith(".xls")) {
POIFSFileSystem fileSystem = new POIFSFileSystem(fileInputStream);
workbook = new HSSFWorkbook(fileSystem);
} else {
throw new RuntimeException("错误提示: 您设置的Excel文件名不合法!");
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取第一个Sheet中的单元格
* @param rowIndex
* @param colIndex
* @return
*/
public static Cell getCellInSheet(int rowIndex, int colIndex) {
sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rowIndex - 1);
Cell cell = row.getCell(colIndex - 1);
return cell ;
}
/**
* 获取指定Sheet中的单元格
* @param st
* @param rowIndex
* @param colIndex
* @return
*/
public static Cell getCellInSheet(int st,int rowIndex, int colIndex) {
sheet = workbook.getSheetAt(st);
Row rowst = sheet.getRow(rowIndex - 1);
if(rowst == null) {
rowst.setRowNum(Cell.CELL_TYPE_STRING);
Cell cellst = rowst.getCell(colIndex - 1);
return cellst ;
}
Cell cellst = rowst.getCell(colIndex - 1);
return cellst ;
}
/**
* 根据行和列获取单元格内容
* @param filePath文件路径
* @param rowIndex行号
* @param colIndex列号
* @return
* @throws Exception
*/
public static String getCellValueAt(String filePath,int rowIndex, int colIndex) throws Exception {
insureExcelType(filePath);
Cell cell = getCellInSheet(rowIndex,colIndex);
String cellValue = getCellValue(cell);
return cellValue ;
}