POI操作excel

package foo;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.Date;


import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;


public class PoiTest
{


@Test
public void testCreate() throws IOException
{
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();


}


@Test
public void testCreateSheet() throws IOException
{
Workbook wb = new HSSFWorkbook();
wb.createSheet("第一个sheet");
wb.createSheet("第2 个sheet");
FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();


}


@Test
public void testCreateCell() throws IOException
{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("第一个sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("1234");
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(1.2f);
row.createCell(3).setCellValue(false);
FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();


}


@Test
public void testCreateCellDate() throws IOException
{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("第一个sheet");
Row row = sheet.createRow(0);
/*
* Cell cell=row.createCell(0); cell.setCellValue("1234");
* row.createCell(1).setCellValue(1.2);
* row.createCell(2).setCellValue(1.2f);
* row.createCell(3).setCellValue(false);
*/
row.createCell(3).setCellValue(new Date());
FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();


}


@Test
public void testCreateCellForMate() throws IOException
{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("第一个sheet");
Row row = sheet.createRow(0);


CreationHelper creationHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(
"yyyy-mm-dd hh:mm:ss"));
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);


cell = row.createCell(1);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);


FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();


}


@Test
public void testdifferentformate() throws IOException
{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("第一个sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("的撒范德萨发的撒地方是");
row.createCell(3).setCellValue(HSSFCell.ENCODING_UNCHANGED);


FileOutputStream fileOutputStream = new FileOutputStream(
"e:\\用poi搞出来的工作部.xls");
wb.write(fileOutputStream);
fileOutputStream.close();
}


@Test
public void testbianLiGongzuobu() throws IOException
{
InputStream iStream = new FileInputStream("e:\\用poi搞出来的工作部.xls");
POIFSFileSystem fSystem = new POIFSFileSystem(iStream);


HSSFWorkbook wb = new HSSFWorkbook(fSystem);
HSSFSheet hssfSheet = wb.getSheetAt(0);
if (hssfSheet == null)
{
return;
}
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++)
{
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null)
{
continue;
}
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++)


{
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if (hssfCell == null)
{
continue;
}
System.out.print("" + getValue(hssfCell));
}
System.out.println();


}
}


private static String getValue(HSSFCell hssfCell)
{
if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
{
return String.valueOf(hssfCell.getBooleanCellValue());
}
else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
return String.valueOf(hssfCell.getNumericCellValue());
}
else
{
return String.valueOf(hssfCell.getStringCellValue());
}
}


@Test
public void testWenbentiqu() throws IOException
{
InputStream iStream = new FileInputStream("e:\\用poi搞出来的工作部.xls");
POIFSFileSystem fSystem = new POIFSFileSystem(iStream);
HSSFWorkbook wb = new HSSFWorkbook(fSystem);


ExcelExtractor excelExtractor = new ExcelExtractor(wb);
excelExtractor.setIncludeSheetNames(false);
System.out.println(excelExtractor.getText());
}


@Test
public void testDuiqifangshi() throws IOException
{
Workbook wb = new HSSFWorkbook(); // ����һ���µĹ�����
Sheet sheet = wb.createSheet("ssss"); // ������һ��Sheetҳ
Row row = sheet.createRow(2); // ����һ����
row.setHeightInPoints(30);


createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER,
HSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_FILL,
HSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_LEFT,
HSSFCellStyle.VERTICAL_TOP);
createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_RIGHT,
HSSFCellStyle.VERTICAL_TOP);


FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();


}


private static void createCell(Workbook wb, Row row, short column,
short halign, short valign)
{
Cell cell = row.createCell(column); // ������Ԫ��
cell.setCellValue(new HSSFRichTextString("Align It")); // ����ֵ
CellStyle cellStyle = wb.createCellStyle(); // ������Ԫ����ʽ
cellStyle.setAlignment(halign); // ���õ�Ԫ��ˮƽ������䷽ʽ
cellStyle.setVerticalAlignment(valign); // ���õ�Ԫ��ֱ������䷽ʽ
cell.setCellStyle(cellStyle); // ���õ�Ԫ����ʽ
}


@Test
public void testbiankuang() throws IOException
{
Workbook wb = new HSSFWorkbook(); // ����һ���µĹ�����
Sheet sheet = wb.createSheet("ssss"); // ������һ��Sheetҳ
Row row = sheet.createRow(2); // ����һ����
Cell cell = row.createCell(1);
cell.setCellValue(4);


sheet.addMergedRegion(new CellRangeAddress(1, // 起始行
2, // 结束行
1, // 其实列
2 // 结束列
));
CellStyle cStyle = wb.createCellStyle();
cStyle.setBorderBottom(CellStyle.BORDER_THIN);
cStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(cStyle);
FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();


}


@Test
public void testFont() throws IOException
{
Workbook wb = new HSSFWorkbook(); // ����һ���µĹ�����
Sheet sheet = wb.createSheet("ssss"); // ������һ��Sheetҳ
Row row = sheet.createRow(1); // ����һ����
Cell cell = row.createCell(1);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
CellStyle cStyle = wb.createCellStyle();
cStyle.setFont(font);
cell.setCellValue(1234);
cell.setCellStyle(cStyle);


FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();
}


@Test
public void testDuquchognxingongzuobu() throws IOException
{
InputStream iStream = new FileInputStream("e:\\用poi搞出来的工作部.xls");
POIFSFileSystem fSystem = new POIFSFileSystem(iStream);
HSSFWorkbook wb = new HSSFWorkbook(fSystem);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(6);
if (cell == null)
{
cell = row.createCell(3);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("测试单元格");
FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();
}


@Test
public void testHuanhang() throws IOException
{
Workbook wb = new HSSFWorkbook(); // ����һ���µĹ�����
Sheet sheet = wb.createSheet("ssss"); // ������һ��Sheetҳ
Row row = sheet.createRow(2); // ����һ����
Cell cell = row.createCell(2);
cell.setCellValue("我要换行 \n llll");
CellStyle cStyle = wb.createCellStyle();
cStyle.setWrapText(true);
cell.setCellStyle(cStyle);


row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints());
sheet.autoSizeColumn(2);


FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();
}


@Test
public void testshujugeshi() throws IOException
{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页");  // 创建第一个Sheet页
CellStyle style;
DataFormat format=wb.createDataFormat();
Row row;
Cell cell;
short rowNum=0;
short colNum=0;

row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(111111.25);

style=wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0")); // 设置数据格式
cell.setCellStyle(style);

row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(1111111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.000"));
cell.setCellStyle(style);
FileOutputStream fileOut = new FileOutputStream("E:\\SSS.xls");
wb.write(fileOut);
fileOut.close();

}
}