所以我正在创建一个HSSFSheet,它使用apache poi和自己的低级代码设置背景位图. https://www.openoffice.org/sc/excelfileformat.pdf声明BITMAP,BIFF8:
Pixel data (array of height lines of the bitmap, from bottom line to top line, see below)
…
In each line all pixels are written from left to right. Each pixel is stored as 3-byte array: the red, green, and blue component of the colour of the pixel, in this order. The size of each line is aligned to multiples of 4 by inserting zero bytes after the last pixel.
有关完整声明,请参见PDF图片:
为了实现这一点,我的方法是使用类型为BufferedImage.TYPE_3BYTE_BGR的java.awt.image.BufferedImage.然后从BufferedImage的栅格中以正确的顺序(从底线到顶线)获取所有字节R G B,并将其填充到宽度为4的多个(x方向).
看代码:
import java.io.FileOutputStream;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.record.StandardRecord;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.util.LittleEndianOutput;
import java.lang.reflect.Field;
import java.util.List;
import java.util.ArrayList;
import java.util.Arrays;
import java.awt.image.BufferedImage;
import java.awt.Graphics2D;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;
import javax.imageio.ImageIO;
public class CreateExcelHSSFSheetBackgroundBitmap {
static List getBackgroundBitmapData(String filePath) throws Exception {
//see https://www.openoffice.org/sc/excelfileformat.pdf - BITMAP
List data = new ArrayList();
// get file byte data in type BufferedImage.TYPE_3BYTE_BGR
BufferedImage in = ImageIO.read(new FileInputStream(filePath));
BufferedImage image = new BufferedImage(in.getWidth(), in.getHeight(), BufferedImage.TYPE_3BYTE_BGR);
Graphics2D graphics = image.createGraphics();
graphics.drawImage(in, null, 0, 0);
graphics.dispose();
short width = (short)image.getWidth();
short height = (short)image.getHeight();
// each pixel has 3 bytes but the width bytes must be filled up to multiple of 4
int widthBytesMultOf4 = (int)((width * 3 + 3) / 4 * 4);
// --- this part takes much time but I have not found any better possibility
// put the bytes R G B into the data; lines of the bitmap must be from bottom line to top line
int bytes = 0;
for (short y = (short)(height - 1); y >= 0; y--) {
for (short x = 0; x < width; x++) {
int r = image.getData().getSample(x, y, 2);
data.add(Byte.valueOf((byte)r));
bytes++;
int g = image.getData().getSample(x, y, 1);
data.add(Byte.valueOf((byte)g));
bytes++;
int b = image.getData().getSample(x, y, 0);
data.add(Byte.valueOf((byte)b));
bytes++;
}
// fill up x with 0 bytes up to multiple of 4
for (int x = width * 3; x < widthBytesMultOf4; x++) {
data.add(Byte.valueOf((byte)0));
bytes++;
}
}
// ---
// size 12 bytes (additional headers, see below) + picture bytes
int size = 12 + bytes;
// get size int as LITTLE_ENDIAN bytes
ByteBuffer bSize = ByteBuffer.allocate(4);
bSize.order(ByteOrder.LITTLE_ENDIAN);
bSize.putInt(size);
// get width short as LITTLE_ENDIAN bytes
ByteBuffer bWidth = ByteBuffer.allocate(2);
bWidth.order(ByteOrder.LITTLE_ENDIAN);
bWidth.putShort(width);
// get height short as LITTLE_ENDIAN bytes
ByteBuffer bHeight = ByteBuffer.allocate(2);
bHeight.order(ByteOrder.LITTLE_ENDIAN);
bHeight.putShort(height);
// put the record headers into the data
Byte[] dataPart = new Byte[] { 0x09, 0x00, 0x01, 0x00,
bSize.array()[0], bSize.array()[1], bSize.array()[2], bSize.array()[3], // size
//now 12 bytes follow
0x0C, 0x00, 0x00, 0x00,
bWidth.array()[0], bWidth.array()[1], // width
bHeight.array()[0], bHeight.array()[1], // height
0x01, 0x00, 0x18, 0x00
};
data.addAll(0, Arrays.asList(dataPart));
return data;
}
public static void main(String[] args) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");
sheet = workbook.createSheet("Sheet2"); // this sheet gets the background image set
// we need the binary records of the sheet
// get InternalSheet
Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
_sheet.setAccessible(true);
InternalSheet internalsheet = (InternalSheet)_sheet.get(sheet);
// get List of RecordBase
Field _records = InternalSheet.class.getDeclaredField("_records");
_records.setAccessible(true);
@SuppressWarnings("unchecked")
List records = (List)_records.get(internalsheet);
// get bytes of the image file
List data = getBackgroundBitmapData("dummyText.png"); //PNG must not have transparency
// do creating BitmapRecord and ContinueRecords from the data in parts of 8220 bytes
BitmapRecord bitmapRecord = null;
List continueRecords = new ArrayList();
int bytes = 0;
if (data.size() > 8220) {
bitmapRecord = new BitmapRecord(data.subList(0, 8220));
bytes = 8220;
while (bytes < data.size()) {
if ((bytes + 8220) < data.size()) {
continueRecords.add(new ContinueRecord(data.subList(bytes, bytes + 8220)));
bytes += 8220;
} else {
continueRecords.add(new ContinueRecord(data.subList(bytes, data.size())));
break;
}
}
} else {
bitmapRecord = new BitmapRecord(data);
}
// add the records after PageSettingsBlock
int i = 0;
for (RecordBase r : records) {
if (r instanceof org.apache.poi.hssf.record.aggregates.PageSettingsBlock) {
break;
}
i++;
}
records.add(++i, bitmapRecord);
for (ContinueRecord continueRecord : continueRecords) {
records.add(++i, continueRecord);
}
// debug output
for (RecordBase r : internalsheet.getRecords()) {
System.out.println(r);
}
// write out workbook
workbook.write(new FileOutputStream("CreateExcelHSSFSheetBackgroundBitmap.xls"));
workbook.close();
}
static class BitmapRecord extends StandardRecord {
//see https://www.openoffice.org/sc/excelfileformat.pdf - BITMAP
List data = new ArrayList();
BitmapRecord(List data) {
this.data = data;
}
public int getDataSize() {
return data.size();
}
public short getSid() {
return (short)0x00E9;
}
public void serialize(LittleEndianOutput out) {
for (Byte b : data) {
out.writeByte(b);
}
}
}
static class ContinueRecord extends StandardRecord {
//see https://www.openoffice.org/sc/excelfileformat.pdf - CONTINUE
List data = new ArrayList();
ContinueRecord(List data) {
this.data = data;
}
public int getDataSize() {
return data.size();
}
public short getSid() {
return (short)0x003C;
}
public void serialize(LittleEndianOutput out) {
for (Byte b : data) {
out.writeByte(b);
}
}
}
}
代码可以工作但是之间的部分
// --- this part takes much time but I have not found any better possibility
和
// ---
需要花费很多时间,因为根据上述奇怪的格式,需要获得每个单个像素的3个字节R G B.
有谁知道更好的方法?也许以上奇怪的格式并不像我想的那样奇怪,而且已经有其他用法了吗?