java 使用工具读取Excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.junl.scott;
import java.io.File;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelImportJxl
{
static String sourceFile = "D:/功能说明文档.xls"; // 源文件
public static void main(String[] args)
{
try
{
Workbook book = Workbook.getWorkbook(new File(sourceFile));
// 0代表第一个工作表对象
Sheet sheet = book.getSheet(0);
int rows = sheet.getRows();
int cols = sheet.getColumns();
String colname1 = sheet.getCell(0, 0).getContents().trim();
String colname2 = sheet.getCell(1, 0).getContents().trim();
String colname3 = sheet.getCell(2, 0).getContents().trim();
System.out.println(colname1 + "," + colname2 + "," + colname3);
for (int z = 1; z < rows; z++)
{
// 0代表列数，z代表行数
for (int j = 0; j < cols; j++)
{
System.out.print(sheet.getCell(j, z).getContents() + "
");
}
System.out.println();
System.out.println("***************************************");
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
package com.junl.scott;
import java.io.File;
import java.util.Date;
import jp.ne.so_net.ga2.no_ji.jcom.excel8.*;
import jp.ne.so_net.ga2.no_ji.jcom.*;
public class ExcelImportJcom
{
public static void main(String[] args) throws Exception
{
JCOMReadExcel();
// JCOMCreateExcel();
}
static void JCOMReadExcel()
{
ReleaseManager rm = new ReleaseManager();
try
{
System.out.println(System.getProperty("java.library.path"));
System.out.println("EXCEL startup...");
// if already started, open new window
ExcelApplication excel = new ExcelApplication(rm);
excel.Visible(false);
String Filename = "D:/物资经销分中心补库申请人、审核人、审批人编号、名字、权限.xls";
ExcelWorkbooks xlBooks = excel.Workbooks();
ExcelWorkbook xlBook = xlBooks.Open(Filename);
ExcelWorksheets xlSheets = xlBook.Worksheets();
//
int item = xlSheets.Count();
// 第一个工作表
ExcelWorksheet xlSheet = xlSheets.Item(1);
System.out.println(xlSheet.CodeName());
ExcelRange xlRange = xlSheet.Cells();
int t = xlSheet.Rows().Count();
int h = xlSheet.Cells().Count();
System.out.println(item + "--------------" + t + "------------" + h);
int i;
int j;
for (j = 1; j <= 10; j++)
{
for (i = 1; i <= 5; i++)
{
System.out.print(xlRange.Item(j, i).Value());
if (i < t)
{
System.out.print(",");
}
}
System.out.println("");
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
rm.release();
}
}
static void JCOMCreateExcel()
{
ReleaseManager rm = new ReleaseManager();
try
{
System.out.println("EXCEL startup...");
// if already started, open new window
ExcelApplication excel = new ExcelApplication(rm);
excel.Visible(true);
// display any information
System.out.println("Version=" + excel.Version());
System.out.println("UserName=" + excel.UserName());
System.out.println("Caption=" + excel.Caption());
System.out.println("Value=" + excel.Value());
ExcelWorkbooks xlBooks = excel.Workbooks();
ExcelWorkbook xlBook = xlBooks.Add(); // create new book
// enumurate all files
System.out.println("set infomation of files in current directory to cell ...");
ExcelWorksheets xlSheets = xlBook.Worksheets();
ExcelWorksheet xlSheet = xlSheets.Item(1);
ExcelRange xlRange = xlSheet.Cells();
xlRange.Item(1, 1).Value("filename");
xlRange.Item(1, 2).Value("size");
xlRange.Item(1, 3).Value("last modified time");
xlRange.Item(1, 4).Value("is directory");
xlRange.Item(1, 5).Value("is file");
xlRange.Item(1, 6).Value("can read");
xlRange.Item(1, 7).Value("can write");
File path = new File("D:/");
String[] filenames = path.list();
for (int i = 0; i < filenames.length; i++)
{
File file = new File(filenames[i]);
System.out.println(file);
xlRange.Item(i + 2, 1).Value(file.getName());
xlRange.Item(i + 2, 2).Value((int) file.length());
xlRange.Item(i + 2, 3).Value(new Date(file.lastModified()));
xlRange.Item(i + 2, 4).Value(file.isDirectory() ? "Yes" : "No");
xlRange.Item(i + 2, 5).Value(file.isFile() ? "Yes" : "No");
xlRange.Item(i + 2, 6).Value(file.canRead() ? "Yes" : "No");
xlRange.Item(i + 2, 7).Value(file.canWrite() ? "Yes" : "No");
}
char start = 'B';
char end = (char) ((byte) start + filenames.length - 1);
System.out.println("end=[" + end + "]");
String expression = "=Sum(B2:" + String.valueOf(end) + "2)";
System.out.println("expression=[" + expression + "]");
System.out.println("embed equation, calculate sum of filesize: " + expression);
xlRange.Item(1, filenames.length + 2).Value("sum");
xlRange.Item(2, filenames.length + 2).Formula(expression);
xlRange.Columns().AutoFit(); // fit columns
// comment out, if print out.
// output default printer.
// System.out.println("print out...");
// xlSheet.PrintOut();
// comment out, if book save to file.
// if no path, save to(My Documents)
// System.out.println
// ("save to file... (My Documents)\testExcel.xls");
xlBook.SaveAs("D:/testExcel.xls");
xlBook.Close(false, null, false);
excel.Quit();
System.out.println("thank you .");
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
rm.release();
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
package com.junl.scott;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
public class ExcelImportPoi
{
public static void main(String[] args) throws Exception
{
File file = new File("D:/功能说明文档.xls");
String[][] result = getData(file, 1);
int rowLength = result.length;
for (int i = 0; i < rowLength; i++)
{
for (int j = 0; j < result[i].length; j++)
{
System.out.print(result[i][j] + "tt");
}
System.out.println();
}
}
/**
*
* 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
*
* @param file
*
读取数据的源Excel
*
* @param ignoreRows
*
读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
*
* @return 读出的Excel中数据的内容
*
* @throws FileNotFoundException
*
* @throws IOException
*/
public static String[][] getData(File file, int ignoreRows)
throws FileNotFoundException, IOException
{
List<String[]> result = new ArrayList<String[]>();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++)
{
HSSFSheet st = wb.getSheetAt(sheetIndex);
// 第一行为标题，不取
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++)
{
HSSFRow row = st.getRow(rowIndex);
if (row == null)
{
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize)
{
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++)
{
String value = "";
cell = row.getCell(columnIndex);
if (cell != null)
{
// 注意：一定要设成这个，否则可能会出现乱码
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
if (date != null)
{
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
}
else
{
value = "";
}
}
else
{
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals(""))
{
value = cell.getStringCellValue();
}
else
{
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals(""))
{
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue)
{
result.add(values);
}
}
}
in.close();
String[][] returnArray = new String[result.size()][rowSize];
for (int i = 0; i < returnArray.length; i++)
{
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
/**
*
* 去掉字符串右边的空格
*
* @param str
*
要处理的字符串
*
* @return 处理后的字符串
*/
public static String rightTrim(String str)
{
if (str == null)
{
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--)
{
if (str.charAt(i) != 0x20)
{
break;
}
length--;
}
return str.substring(0, length);
}
}