POI设置Excel的格式 字体样式 美化
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.HSSFRichTextString;
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.util.HSSFColor;
/**
*
* @author hadeslee
*/
public class Test2{
/** Creates a new instance of Test2 */
public Test2() {
}
public static void main(String[] args)throws Exception {
//声明一个工作薄
HSSFWorkbook wb=new HSSFWorkbook();
//生成一个表格
HSSFSheet sheet=wb.createSheet("表格1");
//生成一个列
HSSFRow row=sheet.createRow(0);
//生成一个样式
HSSFCellStyle style=wb.createCellStyle();
//设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//生成一个字体
HSSFFont font=wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short)16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把字体应用到当前的样式
style.setFont(font);
//填充单元格
for(short i=0;i<5;i++){
//声明一个单元格
HSSFCell cell=row.createCell(i);
//设置单元格的字符值
cell.setCellValue(new HSSFRichTextString("第"+i+"列"));
//设置单元格的样式
cell.setCellStyle(style);
}
FileOutputStream fout=new FileOutputStream("我的第一个EXCEL.xls");
//输出到文件
wb.write(fout);
fout.close();
}
}
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 25);//目的是想把行高设置成25px
FileOutputStream fileOut = new FileOutputStream("c:\\a.xls");
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
打开a.xls发现结果不是我想要的,第一行的高度都没有,没有报错说明代码有问题,为什么回没有高度呢?是不是单位不一样呢?我把row.setHeight((short) 25);改成了row.setHeight((short) 250);结果发现第一行出来了,但是这是怎么一个换算关系呢?我查看了一下导出的Excel第一行高是16像素,换算一下得出row.setHeight((short) 15.625);表示行高为一个像素,那么想设成几个像素就好做了。比如
row.setHeight((short) (15.625*n));//n为行高的像素数。
其实在API中还有一个HSSFRow 对象还有一个设置行高的函数setHeightInPoints(float height);这个函数中参数就是行高的像素数,比setHeight函数要方便多了。
行高设置完成了,接下来设置列宽
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 250);
sheet.setColumnWidth((short) 0, (short) 250);
FileOutputStream fileOut = new FileOutputStream("c:\\a.xls");
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
接下来说说sheet.setColumnWidth((short) 0, (short) 250);
第一个参数表示要为第几列设置,第二个参数表示列的宽度,看看上面的代码按说第一行第一列的单元格形状应该是个正方形,因为宽和高都是250,但是打开导出后的Excel发现宽度没有高度大,是个长方形,查看该列的宽度仅为7个像素,看来行高和列宽的单位是不一样的,同样换一算sheet.setColumnWidth((short) 0, (short) (35.7));表示高度为一个像素,同样设置列宽的像素为sheet.setColumnWidth((short) 0, (short) (35.7*n));//n为列高的像素数。
public class MergedCells {
public static void main(String[] args) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new Region(1, (short) 1, 1, (short) 2));
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
public class WorkingWithFonts {
public static void main(String[] args) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Create a new font and alter it.
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
// Fonts are set into a style so create a new one to use.
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
HSSFRow row = sheet2.createRow(2);
row.setHeightInPoints(240);
sheet2.setColumnWidth((short) 2, (short) 9000);
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.HSSFRichTextString;
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.util.HSSFColor;
/**
*
* @author hadeslee
*/
public class Test2{
/** Creates a new instance of Test2 */
public Test2() {
}
public static void main(String[] args)throws Exception {
//声明一个工作薄
HSSFWorkbook wb=new HSSFWorkbook();
//生成一个表格
HSSFSheet sheet=wb.createSheet("表格1");
//生成一个列
HSSFRow row=sheet.createRow(0);
//生成一个样式
HSSFCellStyle style=wb.createCellStyle();
//设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//生成一个字体
HSSFFont font=wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short)16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把字体应用到当前的样式
style.setFont(font);
//填充单元格
for(short i=0;i<5;i++){
//声明一个单元格
HSSFCell cell=row.createCell(i);
//设置单元格的字符值
cell.setCellValue(new HSSFRichTextString("第"+i+"列"));
//设置单元格的样式
cell.setCellStyle(style);
}
FileOutputStream fout=new FileOutputStream("我的第一个EXCEL.xls");
//输出到文件
wb.write(fout);
fout.close();
}
}
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 25);//目的是想把行高设置成25px
FileOutputStream fileOut = new FileOutputStream("c:\\a.xls");
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
打开a.xls发现结果不是我想要的,第一行的高度都没有,没有报错说明代码有问题,为什么回没有高度呢?是不是单位不一样呢?我把row.setHeight((short) 25);改成了row.setHeight((short) 250);结果发现第一行出来了,但是这是怎么一个换算关系呢?我查看了一下导出的Excel第一行高是16像素,换算一下得出row.setHeight((short) 15.625);表示行高为一个像素,那么想设成几个像素就好做了。比如
row.setHeight((short) (15.625*n));//n为行高的像素数。
其实在API中还有一个HSSFRow 对象还有一个设置行高的函数setHeightInPoints(float height);这个函数中参数就是行高的像素数,比setHeight函数要方便多了。
行高设置完成了,接下来设置列宽
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 250);
sheet.setColumnWidth((short) 0, (short) 250);
FileOutputStream fileOut = new FileOutputStream("c:\\a.xls");
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
接下来说说sheet.setColumnWidth((short) 0, (short) 250);
第一个参数表示要为第几列设置,第二个参数表示列的宽度,看看上面的代码按说第一行第一列的单元格形状应该是个正方形,因为宽和高都是250,但是打开导出后的Excel发现宽度没有高度大,是个长方形,查看该列的宽度仅为7个像素,看来行高和列宽的单位是不一样的,同样换一算sheet.setColumnWidth((short) 0, (short) (35.7));表示高度为一个像素,同样设置列宽的像素为sheet.setColumnWidth((short) 0, (short) (35.7*n));//n为列高的像素数。
public class MergedCells {
public static void main(String[] args) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new Region(1, (short) 1, 1, (short) 2));
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
public class WorkingWithFonts {
public static void main(String[] args) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Create a new font and alter it.
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
// Fonts are set into a style so create a new one to use.
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
HSSFRow row = sheet2.createRow(2);
row.setHeightInPoints(240);
sheet2.setColumnWidth((short) 2, (short) 9000);