Java Excel是开源项目,使用它可以用纯java来读取Excel文件的内容、创建Excel文件、更新已存在的Excel文件的内容,它不依赖于操作系统,这样可以使大家放心方便的操作Excel'文件。
一、maven依赖
<dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
二、excel的读取
打印注释:
public void printComment(Sheet sheet)
{
for (int i = 0; i < sheet.getRows(); i++)
{
Cell[] row = sheet.getRow(i);
for (int j = 0; j < row.length; j++)
{
Cell cell = row[j];
System.out.println(cell.getType());
// 批注
CellFeatures cellFeatures = cell.getCellFeatures();
if (null != cellFeatures && StringUtils.isNotBlank(cellFeatures.getComment()))
{
String pattern = "表格的 ''{0}''行 ''{1}''列存在注释,注释的内容是: {2}";
System.out.println(MessageFormat.format(pattern, new Object[] { i, j, cellFeatures.getComment() }));
}
}
}
}
打印超链接
public void printHylink(Sheet sheet) throws IOException
{
Hyperlink[] hyperlinks = sheet.getHyperlinks();
for (Hyperlink link : hyperlinks)
{
String pattern = "表格的 ''{0}''行 ''{1}''列存在超链接,文件的url是: {2}}";
System.out.println(MessageFormat.format(pattern,
new Object[] { link.getRow(), link.getColumn(), link.getURL() }));
if (null != link.getFile())
{
System.out.println("超链接的文件路径是: " + link.getFile().getCanonicalPath());
}
}
}
打印图片:
public void printImage(Sheet sheet) throws IOException
{
int numberOfImages = sheet.getNumberOfImages();
for (int i = 0; i < numberOfImages; i++)
{
Image drawing = sheet.getDrawing(i);
String pattern = "图片的宽度为 ''{0}'',高度为 ''{1}''";
System.out.println(MessageFormat.format(pattern, new Object[] { drawing.getWidth(), drawing.getHeight() }));
// 图片copy出来
File file = new File("test" + i + ".png");
IOUtils.write(drawing.getImageData(), new FileOutputStream(file));
}
}
三、Excel写入
添加cell:
// 1.添加Label对象
Label labelC = new Label(0, 0, "This is a Label cell");
sheet.addCell(labelC);
// 添加带有字型Formatting的对象
WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
WritableCellFormat wcfF = new WritableCellFormat(wf);
Label labelCF = new Label(1, 0, "This is a Label Cell", wcfF);
sheet.addCell(labelCF);
// 添加带有字体颜色Formatting的对象
WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.RED);
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
Label labelCFC = new Label(2, 0, "This is a Label Cell", wcfFC);
sheet.addCell(labelCFC);
// 2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
sheet.addCell(labelN);
// 添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
sheet.addCell(labelNF);
// 3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
sheet.addCell(labelB);
// 4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
sheet.addCell(labelDT);
// 添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
sheet.addCell(labelDTF);
添加图片:
public void writeImage(WritableSheet sheet) throws IOException
{
File rootFile = new File(System.getProperty("user.dir"));
Collection<File> listFiles = FileUtils.listFiles(rootFile, new String[] { "png" }, false);
for (File file : listFiles)
{
/************************ Begin 这段复杂的算法来计算图片的高度和宽度,网上copy的 **************/
// 开始位置
double picBeginCol = 3;
double picBeginRow = 3;
// 图片时间的高度,宽度
double picCellWidth = 0.0;
double picCellHeight = 0.0;
// 读入图片
BufferedImage picImage = ImageIO.read(file);
// 取得图片的像素高度,宽度
int picWidth = picImage.getWidth();
int picHeight = picImage.getHeight();
// 计算图片的实际宽度
int picWidth_t = picWidth * 32; // 具体的实验值,原理不清楚。
for (int x = 0; x < 1234; x++)
{
int bc = (int) Math.floor(picBeginCol + x);
// 得到单元格的宽度
int v = sheet.getColumnView(bc).getSize();
double offset0_t = 0.0;
if (0 == x)
offset0_t = (picBeginCol - bc) * v;
if (0.0 + offset0_t + picWidth_t > v)
{
// 剩余宽度超过一个单元格的宽度
double ratio_t = 1.0;
if (0 == x)
{
ratio_t = (0.0 + v - offset0_t) / v;
}
picCellWidth += ratio_t;
picWidth_t -= (int) (0.0 + v - offset0_t);
}
else
{ // 剩余宽度不足一个单元格的宽度
double ratio_r = 0.0;
if (v != 0)
ratio_r = (0.0 + picWidth_t) / v;
picCellWidth += ratio_r;
break;
}
}
// 计算图片的实际高度
int picHeight_t = picHeight * 15;
for (int x = 0; x < 1234; x++)
{
int bc = (int) Math.floor(picBeginRow + x);
// 得到单元格的高度
int v = sheet.getRowView(bc).getSize();
double offset0_r = 0.0;
if (0 == x)
offset0_r = (picBeginRow - bc) * v;
if (0.0 + offset0_r + picHeight_t > v)
{
// 剩余高度超过一个单元格的高度
double ratio_q = 1.0;
if (0 == x)
ratio_q = (0.0 + v - offset0_r) / v;
picCellHeight += ratio_q;
picHeight_t -= (int) (0.0 + v - offset0_r);
}
else
{// 剩余高度不足一个单元格的高度
double ratio_m = 0.0;
if (v != 0)
ratio_m = (0.0 + picHeight_t) / v;
picCellHeight += ratio_m;
break;
}
}
/************************ End 这段复杂的算法来计算图片的高度和宽度,网上copy的 **************/
// 生成一个图片对象。
WritableImage image = new WritableImage(picBeginCol, picBeginRow, picCellWidth, picCellHeight, file);
// 把图片插入到sheet
sheet.addImage(image);
}
添加超链接:
调用jxl.write.WritableSheet.addHyperlink(WritableHyperlink)方法
四、更新Excel
更新操作第一步是构造只读的Excel工作薄,第二步是利用已经创建的Excel工作薄创建新的可写入的Excel工作薄,参考下面的代码片段:
Workbook wb = Workbook.getWorkbook(file);
WritableWorkbook wwb = Workbook.createWorkbook(file, wb);
return wwb;