jxl操作エクセル
http://www.iteye.com/topic/624334
エクセルを操作するいくつかの種類のファイルです。
エクセルを操作するいくつかの種類のファイルです。
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
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.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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* excel ， excel excel<br/>
* jar poi-3.5-beta5-20090219.jar<br/>
* excel <br/>
* @author
*
*/
public class ExcelUtils {
// excel
private String path = "";
/**
*
*/
public ExcelUtils() {
}
/**
*
* @param path excel
*/
public ExcelUtils(String path) {
this.path = path;
}
/**
* excel, path
* @param sheetName sheet
* @param fieldName
* @param data
* @throws IOException
*/
public void makeExcel(String sheetName,String[] fieldName,List<String[]> data) throws IOException {
//
HSSFWorkbook workbook = makeWorkBook(sheetName,fieldName,data);
//
String filePath=path.substring(0,path.lastIndexOf("\\"));
// ，
File file = new File(filePath);
//System.out.println(path+"-----------"+file.exists());
if (!file.exists())
file.mkdirs();
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
/**
* excel
* @param excelName excel
* @param sheetName sheet
* @param fieldName
* @param data
* @param response response
*/
public void makeStreamExcel(String excelName, String sheetName,String[] fieldName
, List<String[]> data,HttpServletResponse response) {
OutputStream os = null;
try {
response.reset(); //
os = response.getOutputStream(); //
response.setHeader("Content-disposition", "attachment; filename="
+ new String(excelName.getBytes(), "ISO-8859-1")); //
response.setContentType("application/msexcel"); //
} catch (IOException ex) {//
System.out.println(" :" + ex.getMessage());
}
//
HSSFWorkbook workbook = makeWorkBook(sheetName,fieldName,data);
try {
os.flush();
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
System.out.println("Output is closed");
}
}
/**
* ，
* @param sheetName
* @param fieldName
* @param data
* @return HSSFWorkbook
*/
private HSSFWorkbook makeWorkBook(String sheetName,String[] fieldName
, List<String[]> data)
{
//
HSSFWorkbook workbook = new HSSFWorkbook();
//
HSSFSheet sheet = workbook.createSheet();
// , UTF_16
workbook.setSheetName(0, sheetName);
//
HSSFRow row = sheet.createRow(0);
//
HSSFCell cell;
//
for (int i = 0; i < fieldName.length; i++) {
//
cell = row.createCell((short) i);
//
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// , UTF_16
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//
cell.setCellValue(new HSSFRichTextString(fieldName[i]));
}
// , excel
for (int i = 0; i < data.size(); i++) {
String[] tmp = data.get(i);
//
row = sheet.createRow(i + 1);
for (int j = 0; j < tmp.length; j++) {
cell = row.createCell((short) j);
// String
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString((tmp[j] == null) ? "" : tmp[j]));
}
}
return workbook;
}
public void write(int sheetOrder,int colum, int row, String content) throws Exception {
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(
new FileInputStream(path)));
Sheet sheet = workbook.getSheetAt(sheetOrder);
Row rows = sheet.createRow(row);
Cell cell = rows.createCell(colum);
cell.setCellValue(content);
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
/**
*
* @param sheetOrder
* @return int
* @throws IOException
*/
public int getSheetLastRowNum(int sheetOrder) throws IOException
{
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(
new FileInputStream(path)));
Sheet sheet = workbook.getSheetAt(sheetOrder);
return sheet.getLastRowNum();
}
public String read(int sheetOrder,int colum, int row) throws Exception {
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(
new FileInputStream(path)));
Sheet sheet = workbook.getSheetAt(sheetOrder);
Row rows = sheet.getRow(row);
Cell cell = rows.getCell(colum);
String content = cell.getStringCellValue();
return content;
}
/**
* path ， excel
* @throws IOException
*/
public void makeEmptyExcel() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
//
String filePath=path.substring(0,path.lastIndexOf("\\"));
// ，
File file = new File(filePath);
if (!file.exists())
file.mkdirs();
FileOutputStream fileOut = new FileOutputStream(filePath + "\\" + path.substring(path.lastIndexOf("\\")+1));
wb.write(fileOut);
fileOut.close();
}
/**
* ， ， String[]<br/>
* <br/>
* ， ，
* @param sheetOrder
* @return
* @throws IOException
* @throws
*/
public List<String[]> getDataFromSheet(int sheetOrder) throws IOException
{
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(
new FileInputStream(path)));
Sheet sheet = workbook.getSheetAt(sheetOrder);
List<String[]> strs=new ArrayList<String[]>();
// 0
//System.out.println(sheet.getLastRowNum());
for(int i=0 ; i<=sheet.getLastRowNum() ; i++){
Row rows = sheet.getRow(i);
String[] str =new String[rows.getLastCellNum()];
//
for (int k = 0; k < rows.getLastCellNum(); k++) {
Cell cell = rows.getCell(k);
//
if(0==cell.getCellType()){
// ，
DecimalFormat df=new DecimalFormat("########");
str[k]=df.format(cell.getNumericCellValue());
}
else
str[k] =cell.getStringCellValue();
//System.out.println(cell.getCellType()+"-------------"+str[k]);
}
strs.add(str);
}
return strs ;
}
// public static void main(String[] args) throws Exception {
// ExcelUtils eu=new ExcelUtils("D:\\Tomcat 5.5\\webapps\\sms\\UserFiles\\log\\2009-11-16 16:47:20 .xls");
// List<String[]> ss=new ArrayList<String[]>();
// ss.add(new String[]{" ","sdfds"});
// ss.add(new String[]{" "," "});
// eu.makeExcel("smsLog", new String[]{" "," "}, ss);
// List<String[]> strs=excelUtils.getDataFromSheet(0);
// for (String[] str : strs) {
// for (String s : str) {
// System.out.println(s);
// }
// }
// String content = "Hello Worlds";
// excelUtils.write(2, 3, content);
// String newContent = excelUtils.read(0,1, 1);
// System.out.println(newContent);
// excelUtils.makeEmptyExcel("d:\\a\\ab", "a.xls");
// }
}