package A;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class Excel {
private String outputFile;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private HSSFRow row;
private HSSFFont font;
private HSSFCell cell;
private HSSFCellStyle style;
public Excel(String fileName) {
this.outputFile = fileName;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();
this.style = workbook.createCellStyle();
this.font = workbook.createFont();
this.row = sheet.createRow(0);
this.cell = row.createCell(0);
}
public void createRow(int index) {
this.row = this.sheet.createRow(index);
}
/**
* 填写单元格内容(单元格的列位置,内容,对齐方式)
*
* @param 列位置:index
* @param 内容:value
* @param 对齐方式(1:居中;2:居中左对齐;3:底部居中):choice
*/
public void setCell(int index, String value, int choice) {
cell = this.row.createCell(index);
style = workbook.createCellStyle();
switch (choice) {
case 1:
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
break;
case 2:
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平左对齐
break;
case 3:
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底端对齐
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
break;
case 4:
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//水平右对齐
break;
}
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 写入
*/
public void WriteWorkBook() {
try {
FileOutputStream fOut = new FileOutputStream(outputFile);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
/**
* 读取(行数,列数)
*
* @param 行数:index1
* @param 列数:index2
*/
@SuppressWarnings("resource")
public void ReadWorkBook(int index1, int index2) {
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(outputFile));
// 本例是按名引用(让我们假定那张表有着缺省名"Sheet1")
HSSFSheet sheet = workbook.getSheetAt(0);
// 也可用getSheetAt(int index)按索引引用,
// 在Excel文档中,第一张工作表的缺省索引是0,
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
// 读取左上端单元
HSSFRow row = sheet.getRow(index1);
HSSFCell cell = row.getCell(index2);
System.out.println(cell.getStringCellValue());
} catch (Exception e) {
System.out.println("已运行xlRead() : " + e);
}
}
/**
* 设置字体颜色(颜色short值)
*/
public void SetWordColor(short index) {
font = workbook.createFont();
font.setColor(index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style = workbook.createCellStyle();
style.setFont(font);
}
/**
* 设置列宽(列位置,宽度)
*
* @param 列位置:index
* @param 宽度:value
*/
public void SetColWide(int index, int value) {
sheet.setColumnWidth(index, value);
}
/**
* 设置行高(行位置,高度)
*
* @param 行位置:index
* @param 高度:value
*/
public void SetHeiPoin(int index, int value) {
row = sheet.createRow(index);
row.setHeightInPoints(value);
}
/**
* 设置单元格(颜色short值,单元格边框位置,边框short值)边框默认BORDER_THIN
*
* @param 颜色short值:index
* @param 边框位置(1:上;2:下;3:左;4:右;5:左右为空;6:上下为空)cho
* @param 边框short值ind
*/
public void SetCellColor(short index, int cho, short ind) {
cell = this.row.createCell((int) this.row.getLastCellNum());
style.setFillForegroundColor(index);
style.setFillBackgroundColor(index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
switch (cho) {
case 1:
style.setBorderTop(ind);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
break;
case 2:
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(ind);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
break;
case 3:
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(ind);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
break;
case 4:
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(ind);
break;
case 5:
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
break;
case 6:
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
break;
}
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
}
/**
* 合并单元格,四个参数(起始行,截止行,起始列,截止列)
*
* @param 起始行:startRow
* @param 截止行:endRow
* @param 起始列:startCel
* @param 截止列:endCel
*/
@SuppressWarnings("deprecation")
public void AddMerged(int startRow, int endRow, int startCel, int endCel) {
sheet.addMergedRegion(new CellRangeAddress(startRow, (short) endRow, startCel, (short) endCel));
}
}
后续主类根据需求自己编写,例子仅供参考
package A;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
public class Test_lei {
public static void main(String[] args) {
Excel e = new Excel("D://test.xls");
HSSFColor color;
short style;
e.createRow(0);
e.AddMerged(0, 0, 0, 10);
e.AddMerged(3, 5, 0, 0);
e.AddMerged(3, 5, 1, 1);
e.AddMerged(3, 5, 2, 2);
e.AddMerged(3, 14, 10, 10);
e.AddMerged(6, 8, 0, 0);
e.AddMerged(6, 8, 1, 1);
e.AddMerged(6, 8, 2, 2);
e.AddMerged(9, 11, 0, 0);
e.AddMerged(9, 11, 1, 1);
e.AddMerged(9, 11, 2, 2);
e.AddMerged(12, 14, 0, 0);
e.AddMerged(12, 14, 1, 1);
e.AddMerged(12, 14, 2, 2);
e.SetHeiPoin(0, 20);
e.SetColWide(0, 900);
e.SetColWide(1, 7800);
e.SetColWide(4, 3500);
e.SetColWide(5, 3500);
e.SetColWide(6, 4000);
e.SetColWide(7, 4000);
e.SetColWide(8, 3500);
e.SetColWide(9, 3700);
e.SetColWide(10, 10000);
e.SetColWide(11, 3500);
e.SetColWide(12, 3500);
style = HSSFCellStyle.BORDER_THIN;
color = new HSSFColor.BLACK();
e.SetWordColor(color.getIndex());
e.setCell(0, "中華電信網路容量及使用現況雙週報表", 1);
e.createRow(1);
e.setCell(10, "填表日期:91 年 08 月26日", 2);
e.createRow(2);
e.SetHeiPoin(2, 35);
e.setCell(1, "項 目", 2);
e.setCell(2, "單位", 1);
e.setCell(3, "分公司", 1);
e.createRow(3);
e.setCell(0, "1", 1);
e.setCell(1, "市話交換機門號數(POTS)", 2);
e.setCell(2, "門", 1);
color = new HSSFColor.PALE_BLUE();
e.setCell(3, "北分", 1);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(4, "fhhg", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(5, "yyu", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(6, "yyu", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(7, "yyu", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(8, "yyu", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(9, "yyu", 4);
e.SetCellColor(color.getIndex(), 1, style);
color = new HSSFColor.WHITE();
e.setCell(10, "固網(資料取得截止日期91/07/31)", 2);
e.SetCellColor(color.getIndex(), 1, style);
e.createRow(4);
e.setCell(3, "南分", 1);
e.setCell(4, "ccc", 4);
e.setCell(5, "ccc", 4);
e.setCell(6, "ccc", 4);
e.setCell(7, "ccc", 4);
e.setCell(8, "ccc", 4);
e.setCell(9, "ccc", 4);
e.createRow(6);
color = new HSSFColor.PALE_BLUE();
e.setCell(3, "中分", 1);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(4, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(5, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(6, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(7, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(8, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
e.setCell(9, "bbb", 4);
e.SetCellColor(color.getIndex(), 1, style);
// ArrayList<Integer> list = new ArrayList<Integer>();
// Iterator<Integer> te1 = list.iterator();
// while(te1.hasNext()){
// for(int i = 3;i <= 14;i++){
// e.createRow(i);
// for(int j = 4;j <= 8;j++){
// e.setCell(j, te1.next().toString(), 4);
// }
// }
// }
e.WriteWorkBook();
e.ReadWorkBook(2, 1);
}
}
运行结果:在D盘中生产了一个名为test.xls的文件,打开为: