Java代码 复制代码 收藏代码

  1. import java.io.BufferedInputStream;
  2.  
  3. import java.io.File;
  4.  
  5. import java.io.FileInputStream;
  6.  
  7. import java.io.FileNotFoundException;
  8.  
  9. import java.io.IOException;
  10.  
  11. import java.text.DecimalFormat;
  12.  
  13. import java.text.SimpleDateFormat;
  14.  
  15. import java.util.ArrayList;
  16.  
  17. import java.util.Arrays;
  18.  
  19. import java.util.Date;
  20.  
  21. import java.util.List;
  22.  
  23.  
  24.  
  25. import org.apache.poi.hssf.usermodel.HSSFCell;
  26.  
  27. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  28.  
  29. import org.apache.poi.hssf.usermodel.HSSFRow;
  30.  
  31. import org.apache.poi.hssf.usermodel.HSSFSheet;
  32.  
  33. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  34.  
  35. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  36.  
  37.  
  38.  
  39. public class ExcelOperate {
  40.  
  41.  
  42.  
  43. public static void main(String[] args) throws Exception {
  44.  
  45. File file = new File("ExcelDemo.xls");
  46.  
  47. String[][] result = getData(file, 1);
  48.  
  49. int rowLength = result.length;
  50.  
  51. for(int i=0;i<rowLength;i++) {
  52.  
  53. for(int j=0;j<result[i].length;j++) {
  54.  
  55. System.out.print(result[i][j]+"\t\t");
  56.  
  57. }
  58.  
  59. System.out.println();
  60.  
  61. }
  62.  
  63.  
  64.  
  65. }
  66.  
  67.  
  68.  
  69. public static String[][] getData(File file, int ignoreRows)
  70.  
  71. throws FileNotFoundException, IOException {
  72.  
  73. List<String[]> result = new ArrayList<String[]>();
  74.  
  75. int rowSize = 0;
  76.  
  77. BufferedInputStream in = new BufferedInputStream(new FileInputStream(
  78.  
  79. file));
  80.  
  81. // 打开HSSFWorkbook
  82.  
  83. POIFSFileSystem fs = new POIFSFileSystem(in);
  84.  
  85. HSSFWorkbook wb = new HSSFWorkbook(fs);
  86.  
  87. HSSFCell cell = null;
  88.  
  89. for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
  90.  
  91. HSSFSheet st = wb.getSheetAt(sheetIndex);
  92.  
  93. // 第一行为标题，不取
  94.  
  95. for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
  96.  
  97. HSSFRow row = st.getRow(rowIndex);
  98.  
  99. if (row == null) {
  100.  
  101. continue;
  102.  
  103. }
  104.  
  105. int tempRowSize = row.getLastCellNum() + 1;
  106.  
  107. if (tempRowSize > rowSize) {
  108.  
  109. rowSize = tempRowSize;
  110.  
  111. }
  112.  
  113. String[] values = new String[rowSize];
  114.  
  115. Arrays.fill(values, "");
  116.  
  117. boolean hasValue = false;
  118.  
  119. for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
  120.  
  121. String value = "";
  122.  
  123. cell = row.getCell(columnIndex);
  124.  
  125. if (cell != null) {
  126.  
  127. // 注意：一定要设成这个，否则可能会出现乱码
  128.  
  129. cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  130.  
  131. switch (cell.getCellType()) {
  132.  
  133. case HSSFCell.CELL_TYPE_STRING:
  134.  
  135. value = cell.getStringCellValue();
  136.  
  137. break;
  138.  
  139. case HSSFCell.CELL_TYPE_NUMERIC:
  140.  
  141. if (HSSFDateUtil.isCellDateFormatted(cell)) {
  142.  
  143. Date date = cell.getDateCellValue();
  144.  
  145. if (date != null) {
  146.  
  147. value = new SimpleDateFormat("yyyy-MM-dd")
  148.  
  149. .format(date);
  150.  
  151. } else {
  152.  
  153. value = "";
  154.  
  155. }
  156.  
  157. } else {
  158.  
  159. value = new DecimalFormat("0").format(cell
  160.  
  161. .getNumericCellValue());
  162.  
  163. }
  164.  
  165. break;
  166.  
  167. case HSSFCell.CELL_TYPE_FORMULA:
  168.  
  169. // 导入时如果为公式生成的数据则无值
  170.  
  171. if (!cell.getStringCellValue().equals("")) {
  172.  
  173. value = cell.getStringCellValue();
  174.  
  175. } else {
  176.  
  177. value = cell.getNumericCellValue() + "";
  178.  
  179. }
  180.  
  181. break;
  182.  
  183. case HSSFCell.CELL_TYPE_BLANK:
  184.  
  185. break;
  186.  
  187. case HSSFCell.CELL_TYPE_ERROR:
  188.  
  189. value = "";
  190.  
  191. break;
  192.  
  193. case HSSFCell.CELL_TYPE_BOOLEAN:
  194.  
  195. value = (cell.getBooleanCellValue() == true ? "Y"
  196.  
  197. : "N");
  198.  
  199. break;
  200.  
  201. default:
  202.  
  203. value = "";
  204.  
  205. }
  206.  
  207. }
  208.  
  209. if (columnIndex == 0 && value.trim().equals("")) {
  210.  
  211. break;
  212.  
  213. }
  214.  
  215. values[columnIndex] = rightTrim(value);
  216.  
  217. hasValue = true;
  218.  
  219. }
  220.