Java Excel API的学习

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;