1.创建一个新的Excle工作薄view plain // 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称HSSFSheet sheet = workbook.createSheet();//HSSFSheet sheet = workbook.createSheet("SheetName");
2. 创建新行(row),并将单元格(cell)放入其中。 行号从0开始计算。
view plain HSSFRow row = sheet.createRow((short) 1);3.设置sheet名称和单元格内容为中文view plain wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16);cell.setEncoding((short) 1);cell.setCellValue("中文");4.设置列宽、行高view plain sheet.setColumnWidth((short)column,(short)width);row.setHeight((short)height);5.添加区域,合并单元格view plain Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);sheet.addMergedRegion(region);//得到所有区域sheet.getNumMergedRegions()
6.设置单元格边框格式虚线HSSFCellStyle.BORDER_DOTTED实线HSSFCellStyle.BORDER_THIN view plain public static HSSFCellStyle getCellStyle(short type){ HSSFWorkbook wb = new HSSFWorkbook();HSSFCellStyle style = wb.createCellStyle();style.setBorderBottom(type);//下边框style.setBorderLeft(type);//左边框style.setBorderRight(type);//右边框style.setBorderTop(type);//上边框return style;} 7.设置字体和内容位置view plain HSSFFont f = wb.createFont();f.setFontHeightInPoints((short) 11);//字号f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗style.setFont(f);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中style.setRotation(short rotation);//单元格内容的旋转的角度HSSFDataFormat df = wb.createDataFormat();style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式cell.setCellFormula(string);//给单元格设公式style.setRotation(short rotation);//单元格内容的旋转的角度cell.setCellStyle(style);8.插入图片view plain //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));ImageIO.write(bufferImg,"jpg",byteArrayOut);//读进一个excel模版FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");fs = new POIFSFileSystem(fos);//创建一个工作薄HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);HSSFPatriarch patriarch = sheet.createDrawingPatriarch();HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
例1:java view plain import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;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.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFComment;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
public class PoiCreateExcelTest ……{ public static void main(String[] args) ……{ /** *//** * @see <a href="For>http://poi.apache.org/hssf/quick-guide.html#NewWorkbook">For more</a> */ // 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称HSSFSheet sheet = workbook.createSheet();//HSSFSheet sheet = workbook.createSheet("SheetName");
// 用于格式化单元格的数据HSSFDataFormat format = workbook.createDataFormat();
// 创建新行(row),并将单元格(cell)放入其中。 行号从0开始计算。
HSSFRow row = sheet.createRow((short) 1);
// 设置字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 20); //字体高度font.setColor(HSSFFont.COLOR_RED); //字体颜色font.setFontName("黑体"); //字体font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度font.setItalic(true); //是否使用斜体// font.setStrikeout(true); //是否使用划线
// 设置单元格类型HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中cellStyle.setWrapText(true);