封装对excel的操作,包括本地读写exce
来源:互联网 发布:mac如何剪切文件夹 编辑:程序博客网 时间:2024/06/05 08:14
- 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;
-
-
-
-
-
-
-
- public class ExcelUtils {
-
-
- private String path = "";
-
-
-
- public ExcelUtils() {
-
- }
-
-
-
-
- public ExcelUtils(String path) {
- this.path = path;
- }
-
-
-
-
-
-
-
- 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);
-
- if (!file.exists())
- file.mkdirs();
- FileOutputStream fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- }
-
-
-
-
-
-
-
-
- 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");
- }
- }
-
-
-
-
-
-
-
- private HSSFWorkbook makeWorkBook(String sheetName,String[] fieldName
- , List<String[]> data)
- {
-
- HSSFWorkbook workbook = new HSSFWorkbook();
-
- HSSFSheet sheet = workbook.createSheet();
-
- 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);
-
-
-
- cell.setCellValue(new HSSFRichTextString(fieldName[i]));
- }
-
- 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);
-
- 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();
-
- }
-
-
-
-
-
-
- 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;
- }
-
-
-
-
- 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();
- }
-
-
-
-
-
-
-
-
-
- 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[]>();
-
-
- 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();
-
- }
- strs.add(str);
- }
- return strs ;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- }