https://poi.apache.org/components/spreadsheet/quick-guide.html
1、添加依赖（Maven）
https://mvnrepository.com/artifact/org.apache.poi/poi
class="xml" name="code">
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
代码实现：
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
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;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
// https://poi.apache.org/components/spreadsheet/quick-guide.html
public class Excel_01_Basic {
private static String BASE_PATH = "/Users/tiger/Documents/";
private static String FILE_NAME;
private HSSFWorkbook workBook;
public void createWorkBook() throws Exception{
workBook = new HSSFWorkbook();
//============ header ===================================================
HSSFSheet sheet = workBook.createSheet("2019 Products");
//------------------ 设置列宽
sheet.setColumnWidth(0, 24 * 256); // 24 个字符
//-------------------- 设置行高
Row row = sheet.createRow(2);
row.setHeightInPoints(30);
/**
* HorizontalAlignment.CENTER - one cell
* HorizontalAlignment.CENTER_SELECTION - multiple cell
*/
createCell(workBook, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
createCell(workBook, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
/**
* HorizontalAlignment.FILL - left
* HorizontalAlignment.GENERAL - left
*/
createCell(workBook, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
createCell(workBook, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
createCell(workBook, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
createCell(workBook, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
createCell(workBook, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
//------------------------------ Cell2
// merging cells
int rowIndex = 4;
Row row2 = sheet.createRow(rowIndex);
Cell cell2 = row2.createCell(1);
cell2.setCellValue("This is a test of merging");
CellStyle style2 = workBook.createCellStyle();
//------------------------------ Cell Merging
sheet.addMergedRegion(new CellRangeAddress(
rowIndex, //first row (0-based)
rowIndex, //last row (0-based)
1, //first column (0-based)
8 //last column (0-based)
));
//------------------------------- Heights
row2.setHeightInPoints(141);
//------------------------------- Fonts
// Note, the maximum number of unique fonts in a workbook is limited to 32767.
// You should re-use fonts in your applications instead of creating a font for each cell.
//
Font font_30 = workBook.createFont();
font_30.setFontHeightInPoints((short)30);
font_30.setFontName("Courier New");
font_30.setItalic(true);
font_30.setStrikeout(true);
style2.setFont(font_30);
cell2.setCellStyle(style2);
//--------------------------------- Picture
Row row0 = sheet.createRow(0);
row0.setHeightInPoints(180);
Row row1 = sheet.createRow(1);
row1.setHeightInPoints(180);
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
BufferedImage bufferImg = ImageIO.read(new File("/Users/tiger/Downloads/2018-11-28_16-56-28-node.jpg")); //将图片读到BufferedImage
ImageIO.write(bufferImg, "png", byteArrayOut); // 将图片写入流中
// 利用HSSFPatriarch将图片写入EXCEL
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
/**
* 该构造函数有8个参数
* 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
* 后四个参数，前连个表示图片左上角所在的columnNum和 rowNum，后天个参数对应的表示图片右下角所在的columnNum和rowNum，
* excel中的columnNum和rowNum的index都是从0开始的
*
*/
HSSFClientAnchor anchor0 = new HSSFClientAnchor(0, 0, 950, 245 ,
(short) 0, 0, (short)0, 0);
// 插入图片
patriarch.createPicture(anchor0, workBook.addPicture(byteArrayOut
.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//图片一导出到单元格B2中
HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 950, 245,
(short) 0, 1, (short) 0, 1);
// 插入图片
patriarch.createPicture(anchor1, workBook.addPicture(byteArrayOut
.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
byteArrayOut.close();
//============ footer ===================================================
String safeName = WorkbookUtil.createSafeSheetName("workbook.xls");
FILE_NAME = BASE_PATH + safeName;
try ( OutputStream fileOut = new FileOutputStream(FILE_NAME) ) {
workBook.write(fileOut);
}
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param halign the horizontal alignment for the cell.
* @param valign the vertical alignment for the cell.
*/
private void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
Cell cell = row.createCell(column);
cell.setCellValue("Align It");
CellStyle cellStyle = wb.createCellStyle();
// set alignment
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
// set borders
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
cellStyle.setBorderTop(BorderStyle.MEDIUM_DASHED);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// set background color // Aqua background
// cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
// cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);
// set // Orange "foreground", foreground being the fill foreground not the font color.
cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//
cell.setCellStyle(cellStyle);
}
public static void main(String[] args) {
Excel_01_Basic excelWorkBook = new Excel_01_Basic();
try {
excelWorkBook.createWorkBook();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("excel is created successfully!");
System.out.printf("file path = %s\n", FILE_NAME);
System.out.println("Working Directory = " +
System.getProperty("user.dir"));
}
}
转载请注明，
原文出处：https://lixh1986.iteye.com/blog/2436814
-
- 大小: 165.1 KB
- 大小: 165.1 KB
相关文章
