一、读,excel主流主要是2003和2007
2003:
/**
* 将返回结果存储在ArrayList内,存储结构与二位数组类似
* lists.get(0).get(0)表示过去Excel中0行0列单元格
* @param file
* @return
*/
public static HashMap<String, Object> readExcel2003(File file){
HashMap<String, Object> map = new HashMap<String, Object>();
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Object value;
for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
rowList.add(colList);
}
continue;
}else{
rowCount++;
}
for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
cell = row.getCell(j);
if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
System.out.println(i + "行" + j
+ " 列 is Number type ; DateFormt:"
+ value.toString());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}
colList.add(value);
}
rowList.add(colList);
}
map.put("workbook",wb);
map.put("sheet",sheet);
map.put("rowList",rowList);
return map;
}catch(Exception e){
return null;
}
}
2007:
/**
* excel2007的读取
* @param file
* @return
*/
public static HashMap<String, Object> readExcel2007(File file){
HashMap<String, Object> map = new HashMap<String, Object>();
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Object value;
for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
rowList.add(colList);
}
continue;
}else{
rowCount++;
}
for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
cell = row.getCell(j);
if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
System.out.println(i + "行" + j
+ " 列 is Number type ; DateFormt:"
+ value.toString());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}
colList.add(value);
}
rowList.add(colList);
}
map.put("workbook",wb);
map.put("sheet",sheet);
map.put("rowList",rowList);
return map;
}catch(Exception e){
System.out.println("exception");
return null;
}
}
二、写,写的话也有两种,这里介绍一种,都是一样的,03和07的也就是api不一样,其他的方法都是一样的,这里介绍一种:
/**
* 写excel
* @param result
* @param path
*/
public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){
if(result == null){
return;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
for(int i = 0 ;i < result.size() ; i++){
HSSFRow row = sheet.createRow(i);
if(result.get(i) != null){
for(int j = 10; j < result.get(i).size() ; j++){
HSSFCell cell = row.createCell(j);
cell.setCellValue(result.get(i).get(j).toString());
}
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try
{
wb.write(os);
} catch (IOException e){
e.printStackTrace();
}
byte[] content = os.toByteArray();
File file = new File(path);//Excel文件生成后存储的位置。
OutputStream fos = null;
try
{
fos = new FileOutputStream(file);
fos.write(content);
os.close();
fos.close();
}catch (Exception e){
e.printStackTrace();
}
}
三、有些场景下输出成excel不太方便,这时候可以输出成txt文件,方法如下:
package util;
import java.io.*;
/**
* Created by ayo
*/
public class ReadFromFile {
/**
* 以字节为单位读取文件,常用于读二进制文件,如图片、声音、影像等文件。
*/
public static void readFileByBytes(String fileName) {
File file = new File(fileName);
InputStream in = null;
try {
System.out.println("以字节为单位读取文件内容,一次读一个字节:");
// 一次读一个字节
in = new FileInputStream(file);
int tempbyte;
while ((tempbyte = in.read()) != -1) {
System.out.write(tempbyte);
}
in.close();
} catch (IOException e) {
e.printStackTrace();
return;
}
try {
System.out.println("以字节为单位读取文件内容,一次读多个字节:");
// 一次读多个字节
byte[] tempbytes = new byte[100];
int byteread = 0;
in = new FileInputStream(fileName);
ReadFromFile.showAvailableBytes(in);
// 读入多个字节到字节数组中,byteread为一次读入的字节数
while ((byteread = in.read(tempbytes)) != -1) {
System.out.write(tempbytes, 0, byteread);
}
} catch (Exception e1) {
e1.printStackTrace();
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e1) {
}
}
}
}
/**
* 以字符为单位读取文件,常用于读文本,数字等类型的文件
*/
public static void readFileByChars(String fileName) {
File file = new File(fileName);
Reader reader = null;
try {
System.out.println("以字符为单位读取文件内容,一次读一个字节:");
// 一次读一个字符
reader = new InputStreamReader(new FileInputStream(file));
int tempchar;
while ((tempchar = reader.read()) != -1) {
// 对于windows下,\r\n这两个字符在一起时,表示一个换行。
// 但如果这两个字符分开显示时,会换两次行。
// 因此,屏蔽掉\r,或者屏蔽\n。否则,将会多出很多空行。
if (((char) tempchar) != '\r') {
System.out.print((char) tempchar);
}
}
reader.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
System.out.println("以字符为单位读取文件内容,一次读多个字节:");
// 一次读多个字符
char[] tempchars = new char[30];
int charread = 0;
reader = new InputStreamReader(new FileInputStream(fileName));
// 读入多个字符到字符数组中,charread为一次读取字符数
while ((charread = reader.read(tempchars)) != -1) {
// 同样屏蔽掉\r不显示
if ((charread == tempchars.length)
&& (tempchars[tempchars.length - 1] != '\r')) {
System.out.print(tempchars);
} else {
for (int i = 0; i < charread; i++) {
if (tempchars[i] == '\r') {
continue;
} else {
System.out.print(tempchars[i]);
}
}
}
}
} catch (Exception e1) {
e1.printStackTrace();
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e1) {
}
}
}
}
/**
* 以行为单位读取文件,常用于读面向行的格式化文件
*/
public static void readFileByLines(String fileName) {
File file = new File(fileName);
BufferedReader reader = null;
try {
System.out.println("以行为单位读取文件内容,一次读一整行:");
reader = new BufferedReader(new FileReader(file));
String tempString = null;
int line = 1;
// 一次读入一行,直到读入null为文件结束
while ((tempString = reader.readLine()) != null) {
// 显示行号
System.out.println("line " + line + ": " + tempString);
line++;
}
reader.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e1) {
}
}
}
}
/**
* 随机读取文件内容
*/
public static void readFileByRandomAccess(String fileName) {
RandomAccessFile randomFile = null;
try {
System.out.println("随机读取一段文件内容:");
// 打开一个随机访问文件流,按只读方式
randomFile = new RandomAccessFile(fileName, "r");
// 文件长度,字节数
long fileLength = randomFile.length();
// 读文件的起始位置
int beginIndex = (fileLength > 4) ? 4 : 0;
// 将读文件的开始位置移到beginIndex位置。
randomFile.seek(beginIndex);
byte[] bytes = new byte[10];
int byteread = 0;
// 一次读10个字节,如果文件内容不足10个字节,则读剩下的字节。
// 将一次读取的字节数赋给byteread
while ((byteread = randomFile.read(bytes)) != -1) {
System.out.write(bytes, 0, byteread);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (randomFile != null) {
try {
randomFile.close();
} catch (IOException e1) {
}
}
}
}
/**
* 显示输入流中还剩的字节数
*/
private static void showAvailableBytes(InputStream in) {
try {
System.out.println("当前字节输入流中的字节数为:" + in.available());
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
String fileName = "C:/temp/newTemp.txt";
ReadFromFile.readFileByBytes(fileName);
ReadFromFile.readFileByChars(fileName);
ReadFromFile.readFileByLines(fileName);
ReadFromFile.readFileByRandomAccess(fileName);
}
}
利用下面这个方法进行拼接:
package util;
import java.io.FileWriter;
import java.io.IOException;
import java.io.RandomAccessFile;
/**
* Created by ayo.
*/
public class AppendToFile {
/**
* A方法追加文件:使用RandomAccessFile
*/
public static void appendMethodA(String fileName, String content) {
try {
// 打开一个随机访问文件流,按读写方式
RandomAccessFile randomFile = new RandomAccessFile(fileName, "rw");
// 文件长度,字节数
long fileLength = randomFile.length();
//将写文件指针移到文件尾。
randomFile.seek(fileLength);
randomFile.writeBytes(content);
randomFile.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* B方法追加文件:使用FileWriter
*/
public static void appendMethodB(String fileName, String content) {
try {
//打开一个写文件器,构造函数中的第二个参数true表示以追加形式写文件
FileWriter writer = new FileWriter(fileName, true);
writer.write(content);
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
String fileName = "C:/temp/newTemp.txt";
String content = "new append!";
//按方法A追加文件
AppendToFile.appendMethodA(fileName, content);
AppendToFile.appendMethodA(fileName, "append end. \n");
//显示文件内容
ReadFromFile.readFileByLines(fileName);
//按方法B追加文件
AppendToFile.appendMethodB(fileName, content);
AppendToFile.appendMethodB(fileName, "append end. \n");
//显示文件内容
ReadFromFile.readFileByLines(fileName);
}
}
over!