javaPOI单元格格式设置居中设置颜⾊，设置前景⾊
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_Index());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
设置居中对齐
//设置⽔平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
⾃适应宽度
//⾃动设置⾏⾼
int colLength = StringCellValue().getBytes().length*256; //
sheet.setColumnWidth(col, colLength);
package com.hwt.glmfmon;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
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.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
/**
* 导出Excel公共⽅法
* @version 1.0
*
* @author wangcp
*
*/
public class ExportExcel extends BaseAction {
//显⽰的导出表的标题
private String title;
//导出表的列名
private String[] rowName ;
private List<Object[]> dataList = new ArrayList<Object[]>();
HttpServletResponse response;
//构造⽅法，传⼊要导出的数据
public ExportExcel(String title,String[] rowName,List<Object[]> dataList){
this.dataList = dataList;
this.title = title;
}
/*
* 导出数据
* */
public void export() throws Exception{
try{
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建⼯作簿对象
HSSFSheet sheet = ateSheet(title); // 创建⼯作表
// 产⽣表格标题⾏
HSSFRow rowm = ateRow(0);
HSSFCell cellTiltle = ateCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为⾃定义⽅法 - 在下⾯ - 可扩展】
HSSFCellStyle columnTopStyle = ColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = Style(workbook); //单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = ateRow(2); // 在索引2的位置创建⾏(最顶端的⾏开始的第⼆⾏) // 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = ateCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = (i);//遍历每个对象
HSSFRow row = ateRow(i+3);//创建所需的⾏数
for(int j=0; j<obj.length; j++){
HSSFCell cell = null; //设置单元格的数据类型
if(j == 0){
cell = ateCell(j,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i+1);
}else{
cell = ateCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(obj[j]) && obj[j] != null){
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
}
cell.setCellStyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长⾃动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = ColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < LastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前⾏未被使⽤过
if (Row(rowNum) == null) {
currentRow = ateRow(rowNum);
} else {
currentRow = Row(rowNum);
}
if (Cell(colNum) != null) {
HSSFCell currentCell = Cell(colNum);
if (CellType() == HSSFCell.CELL_TYPE_STRING) {
int length = StringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
if(workbook !=null){
try
{
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; String headStr = "attachment; filename=\"" + fileName + "\"";
response = getResponse();
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = OutputStream();
workbook.write(out);
}
catch (IOException e)
{
e.printStackTrace();
}
}
}catch(Exception e){
e.printStackTrace();
}
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体
HSSFFont font = ateFont();
//设置字体⼤⼩
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = ateCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜⾊;
style.setBottomBorderColor(HSSFColor.BLACK.index);
/
/设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜⾊;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜⾊;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜⾊;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式⽤应⽤设置的字体;
style.setFont(font);
//设置⾃动换⾏;
style.setWrapText(false);
//设置⽔平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style;
}
/*
htmlborder
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = ateFont();
//设置字体⼤⼩
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
/
/设置样式;
HSSFCellStyle style = ateCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜⾊;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜⾊;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜⾊;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜⾊;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式⽤应⽤设置的字体;
style.setFont(font);
//设置⾃动换⾏;
style.setWrapText(false);
/
/设置⽔平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style;
}
}
---------------------
作者：艾伦蓝
来源：CSDN
原⽂：blog.csdn/lan12334321234/article/details/70049738 版权声明：本⽂为博主原创⽂章，转载请附上博⽂链接！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论