本文详细介绍了如何使用Apache POI库通过Maven导入jar包来操作Excel文件，包括读取与写入数据及导出图片到Excel。重点涵盖了使用HSSFWorkbook和XSSFWorkbook类进行文件操作，读取不同格式的Excel文件（如xls和xlsx），并展示了从单元格中获取不同类型数据的方法。此外，文章还介绍了如何将读取的数据写入另一个Excel文件以及如何在Excel中插入图片。
1）使用maven导入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
1.HSSFWorkbook类（Horrible SpreadSheet FormatWorkbook 令人讨厌的扩展文件格式）
实际上对应整个Excel文件，其中包含子页，行、列、单元格等相关信息。
需要传入对应Excel文件的输入流作为参数的POIFSFileSystem
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(new File("src/test/resources/test.xls"))));2.HSSFWorkbook.getNumberOfSheets()
获取Excel文件的子页数目
3.HSSFRow HSSFWorkbook.getSheetAt(sheetIndex)
根据索引获得Excel文件的子页
4.HSSFRow HSSFRow.getRow(rowIndex)
根据索引获得子页中对应的行
3）读取Excel文件工具类，xls和xlsx的读取方法不同
获取Excel中数据import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
class ReadExcel
{
/**
* 用来获取文件后缀，从而判断文件类型
* @param path
* @return
*/
public static String getPostfix(String path)
{
if (path == null || Common.EMPTY.equals(path.trim()))
{
return Common.EMPTY;
}
if (path.contains(Common.POINT))
{
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
}
return Common.EMPTY;
}
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public static String[][] readExcel(String path) throws IOException
{
if (path == null || Common.EMPTY.equals(path))
{
return null;
}
else
{
String postfix = getPostfix(path);
if (!Common.EMPTY.equals(postfix))
{
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix))
{
return readXls(path);
}
else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix))
{
return readXlsx(path);
}
}
else
{
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* 读取Excel 2007\2010
* @param path
* @return
* @throws IOException
*/
private static String[][] readXlsx(String path) throws IOException
{
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
List<String[]> result = new ArrayList<String[]>();
int maxRowSize = 0;
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++)
{
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null)
{
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++)
{
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(null == xssfRow)
continue;
//获取该行有多少个单元格
int tempRowSize = xssfRow.getLastCellNum() + 1;
//获得所有行中单元格的最大数目
if(tempRowSize > maxRowSize)
maxRowSize = tempRowSize;
String[] values = new String[tempRowSize];
Arrays.fill(values, "");
if (xssfRow != null)
{
for(int columnIndex=0; columnIndex<=xssfRow.getLastCellNum(); columnIndex++)
{
XSSFCell cell = xssfRow.getCell(columnIndex);
if(null == cell)
continue;
values[columnIndex] = getValue(cell);
}
}
result.add(values);
}
}
String[][] returnArray = new String[result.size()][maxRowSize];
for (int i = 0; i < returnArray.length; i++)
{
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
/**
* 读取Excel 2003
* @param path the path of the Excel
* @return
* @throws IOException
*/
private static String[][] readXls(String path) throws IOException
{
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
List<String[]> result = new ArrayList<String[]>();
int maxRowSize = 0;
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++)
{
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null)
{
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++)
{
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(null == hssfRow)
continue;
//获取该行有多少个单元格
int tempRowSize = hssfRow.getLastCellNum();
//获得所有行中单元格的最大数目
if(tempRowSize > maxRowSize)
maxRowSize = tempRowSize;
String[] values = new String[tempRowSize];
Arrays.fill(values, "");
if (hssfRow != null)
{
for(int columnIndex=0; columnIndex<=hssfRow.getLastCellNum(); columnIndex++)
{
HSSFCell cell = hssfRow.getCell(columnIndex);
if(null == cell)
continue;
values[columnIndex] = getValue(cell);
}
}
result.add(values);
}
}
String[][] returnArray = new String[result.size()][maxRowSize];
for (int i = 0; i < returnArray.length; i++)
{
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
/**
* 用来从XSSFCell中获取值，不同单元格类型处理方法不同
* @param xssfRow
* @return
*/
@SuppressWarnings("static-access")
private static String getValue(XSSFCell xssfRow)
{
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN)
{
return String.valueOf(xssfRow.getBooleanCellValue());
}
else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC)
{
if(HSSFDateUtil.isCellDateFormatted(xssfRow))
{
Date date = xssfRow.getDateCellValue();
if(date != null)
return new SimpleDateFormat("yyyy-MM-dd").format(date);
else
return "";
}
return new BigDecimal(xssfRow.getNumericCellValue()).toPlainString();
}
else
{
return String.valueOf(xssfRow.getStringCellValue());
}
}
/**
* 用来从HSSFCell中获取值，不同单元格类型处理方法不同
* @param hssfCell
* @return
*/
@SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell)
{
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN)
{
return String.valueOf(hssfCell.getBooleanCellValue());
}
else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC)
{
if(HSSFDateUtil.isCellDateFormatted(hssfCell))
{
Date date = hssfCell.getDateCellValue();
if(date != null)
return new SimpleDateFormat("yyyy-MM-dd").format(date);
else
return "";
}
return new BigDecimal(hssfCell.getNumericCellValue()).toPlainString();
}
else
{
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
class Common
{
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String LIB_PATH = "lib";
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
}
测试：
public class ExcelTest
{
@Test
public void test() throws IOException
{
String filePath = "src/test/zz.xlsx";
String[][] result = ReadExcel.readExcel(filePath);
for (String[] strings : result)
{
for (String string : strings)
{
System.out.print(string+" ");
}
System.out.println();
}
}
}结果为：
AC01001 单笔交易金额大于等于X元 在线消费、POS消费、QPOS消费、POS预授权、QPOS预授权
AC01002 同一账户单日累积成功交易金额超过X元 充值、转账、提现、信用卡还款、银行卡转账、在线消费
AC01003 同一账户单日累计“相同金额交易”大于等于X元 在线消费
AC01041 同一帐户单日发生X笔以上单笔金额在9000-10000之间的交易 充值、转账、提现、消费 、信用卡还款、银行卡转账
AC01044 同一账户单日累积提现金额大于等于X元 提现 从一个Excel中读出的数据写入另一个Excel
String filePath = "src/test/resources/test.xls";
String[][] result = getData(filePath);
//以二维数组的形式输出Excel中内容太
for(String[] rowValue:result)
{
for(String value:rowValue)
{
System.out.print(value+" ");
}
System.out.println();
}
writeData(result,"src/test/resources/test2.xls");private void writeData(String[][] result,String filePath)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("first_sheet");
//设置标题
String[] header = new String[3];
header[0] = "序号";
header[1] = "规则";
header[2] = "业务";
HSSFRow head = sheet.createRow(0);
for(int i=0;i<header.length; i++)
{
HSSFCell cell = head.createCell(i);
cell.setCellValue(new HSSFRichTextString(header[i]));
}
for(int rowIndex=1;rowIndex<=result.length; rowIndex++)
{
HSSFRow row = sheet.createRow(rowIndex);
for(int columnIndex=0; columnIndex<result[rowIndex-1].length; columnIndex++)
{
HSSFCell cell = row.createCell(columnIndex, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(result[rowIndex-1][columnIndex]));
}
}
try
{
FileOutputStream fout = new FileOutputStream(new File(filePath));
workbook.write(fout);
fout.close();
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}5）导出图片到Excel
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.imageio.ImageIO;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class TestExcelImage {
static List<BufferedImage> images = new ArrayList<BufferedImage>();
static {
try {
images.add(ImageIO.read(new File("C:/t/1.png")));
images.add(ImageIO.read(new File("C:/t/2.png")));
images.add(ImageIO.read(new File("C:/t/3.png")));
images.add(ImageIO.read(new File("C:/t/4.png")));
images.add(ImageIO.read(new File("C:/t/5.png")));
images.add(ImageIO.read(new File("C:/t/6.png")));
images.add(ImageIO.read(new File("C:/t/7.png")));
images.add(ImageIO.read(new File("C:/t/8.png")));
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException {
FileOutputStream fileOut = null;
// 创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
short i = 0;
for (BufferedImage image : images) {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "png", byteArrayOut);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
(short) 0, i, (short) 1, 1 + i);
anchor.setAnchorType(0);
// 插入图片
patriarch.createPicture(anchor, wb.addPicture(
byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
i++;
}
fileOut = new FileOutputStream("c:/workbook.xls");
// 写入excel文件
wb.write(fileOut);
fileOut.close();
}
}1.所有图片使用一个HSSFPatriarch就可以了
2.HSSFClientAnchor中的前4个参数表示偏移量,使用0即可;最后四个参
col1 图片的左上角放在第几个列cell，
row1 图片的左上角放在第几个行cell，
col2 图片的右下角放在第几个列cell，
row2 图片的右下角放在第几个行cell，
注意这里的cell是看线的
6）总结
其实也就是操作Io输入输出流的装饰类，并将Excel文件，子页，行，单元格对象化了，更加方便操作
扫一扫
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
