jdbc mysql excel_用JDBC把Excel中的数据导入到Mysql数据库中

1 packageImportData;2

3 importjava.io.FileInputStream;4 importjava.io.FileNotFoundException;5 importjava.io.IOException;6 importjava.io.InputStream;7 importjava.util.Date;8 importjava.util.HashMap;9 importjava.util.Map;10

11 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;12 importorg.apache.poi.ss.usermodel.Cell;13 importorg.apache.poi.ss.usermodel.DateUtil;14 importorg.apache.poi.ss.usermodel.Row;15 importorg.apache.poi.ss.usermodel.Sheet;16 importorg.apache.poi.ss.usermodel.Workbook;17 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;18 importorg.slf4j.Logger;19 importorg.slf4j.LoggerFactory;20

21 importFileUtil.Util;22

23 importcom.mysql.jdbc.Connection;24 import java.sql.*;25

26 public classImportDataFromExcelToMysql {27 private Logger logger = LoggerFactory.getLogger(ImportDataFromExcelToMysql.class);28 privateWorkbook wb;29 privateSheet sheet;30 privateRow row;31 private static String[] filepathlist; //文件名列表

32 private static boolean titleflag = false;//判断标题是否为数据

33

34 publicImportDataFromExcelToMysql(String filepath) {35 if (filepath == null) {36 return;37 }38 String ext = filepath.substring(filepath.lastIndexOf("."));//获取文件格式

39 try{40 InputStream is = newFileInputStream(filepath);41 if (".xls".equals(ext)) {42 wb = newHSSFWorkbook(is);43 } else if (".xlsx".equals(ext)) {44 wb = newXSSFWorkbook(is);45 } else{46 wb = null;47 }48 } catch(FileNotFoundException e) {49 logger.error("FileNotFoundException", e);50 } catch(IOException e) {51 logger.error("IOException", e);52 }53 }54

55 /**

56 * 读取Excel表格表头的内容57 */

58 public String[] readExcelTitle() throwsException {59 titleflag = false;60 if (wb == null) {61 throw new Exception("Workbook对象为空！");62 }63

64 sheet = wb.getSheetAt(0);65 row = sheet.getRow(0);66

67 //标题总列数

68 int colNum =row.getPhysicalNumberOfCells();69 System.out.println(colNum);70 String[] title = newString[colNum];71 for (int i = 0; i < colNum; i++) {72 //title[i] = getStringCellValue(row.getCell((short) i));

73 title[i] =getCellFormatValue(row.getCell(i)).toString();74 }75

76 //判断title第一个字段是否为数字，即可知道title是否为数据

77 Cell cell = row.getCell(0);78 switch (cell.getCellTypeEnum()){//.getCellType()) {

79 caseNUMERIC:80 caseFORMULA: {81 //判断当前的cell是否为Date

82 if(DateUtil.isCellDateFormatted(cell)) {83 titleflag = false;84 } else {//如果是纯数字

85 titleflag = true;86 }87 break;88 }89 case STRING://如果当前Cell的Type为STRING

90 titleflag = false;91 break;92 default://默认的Cell值

93 titleflag = false;94 }95

96 returntitle;97 }98

99 /**

100 * 读取Excel数据内容101 */

102 public Map> readExcelContent() throwsException {103 if (wb == null) {104 throw new Exception("Workbook对象为空！");105 }106 Map> content = new HashMap>();107

108 sheet = wb.getSheetAt(0);109 //得到总行数

110 int rowNum =sheet.getLastRowNum();111 row = sheet.getRow(0);112 int colNum =row.getPhysicalNumberOfCells();113 //正文内容应该从第二行开始,第一行为表头的标题

114 for (int i = 1; i <= rowNum; i++) {115 row =sheet.getRow(i);116 int j = 0;117 Map cellValue = new HashMap();118 while (j

128 /**

129 * 根据Cell类型设置数据130 */

131 privateObject getCellFormatValue(Cell cell) {132 Object cellvalue = "";133 if (cell != null) {134 //判断当前Cell的Type

135 switch (cell.getCellTypeEnum()){//.getCellType()) {

136 caseNUMERIC:137 caseFORMULA: {138 //判断当前的cell是否为Date

139 if(DateUtil.isCellDateFormatted(cell)) {140 //如果是Date类型则，转化为Data格式141 //data格式是带时分秒的：2013-7-10 0:00:00142 //cellvalue = cell.getDateCellValue().toLocaleString();143 //data格式是不带带时分秒的：2013-7-10

144 Date date =cell.getDateCellValue();145 cellvalue =date;146 } else {//如果是纯数字147

148 //取得当前Cell的数值

149 cellvalue = String.valueOf((int)cell.getNumericCellValue());150 }151 break;152 }153 case STRING://如果当前Cell的Type为STRING154 //取得当前的Cell字符串

155 cellvalue = "\""+cell.getRichStringCellValue().getString()+"\"";156 //字符串加上双引号""，否则导入数据时会出错

157 break;158 default://默认的Cell值

159 cellvalue = "";160 }161 } else{162 cellvalue = "";163 }164 returncellvalue;165 }166

167 public static voidmain(String[] args) {168 try{169 //connection

170 Class.forName("com.mysql.jdbc.Driver");171 String url = "jdbc:mysql://localhost:3306/wmj?&useSSL=false"; //设置url，wmj是database

172 Connection conn;//创建连接

173 conn = (Connection)DriverManager.getConnection(url, "root", "root123");//username="root",password = "root123"

174 Statement stmt =conn.createStatement();175

176 filepathlist = Util.fileList("D:\\lzu\\数据预处理\\Data", ".xls,.xlsx");//导入数据文件夹和数据文件类型

177

178

179

180 for(String filepath : filepathlist) {181 //插入数据前

182 String t = "";183 String sql = "select count(*) from tbl_data_bak";184 ResultSet ret =stmt.executeQuery(sql);185 if(ret.next()) {186 System.out.print("count="+ret.getInt(1));187 }188

189 ImportDataFromExcelToMysql excelReader = newImportDataFromExcelToMysql(filepath);190 //对读取Excel表格标题测试

191 String[] title =excelReader.readExcelTitle();192 //System.out.println("获得Excel表格的标题:");

193 for(String s : title) {194 //System.out.print(s + "| ");

195 t += s + ",";196 }197 t = t.substring(0,t.length()-1);198 //System.out.println("title = " + t);199

200 //如果标题是数据，则插入

201 if(titleflag) {202 sql = "insert into tbl_data_bak values(" + t + ");";203 stmt.executeUpdate(sql);204 }205

206 //插入数据后

207 sql = "select count(*) from tbl_data_bak";208 ret =stmt.executeQuery(sql);209 if(ret.next()) {210 System.out.print("count="+ret.getInt(1));211 }212

213 //对读取Excel表格内容测试

214 Map> map =excelReader.readExcelContent();215 //System.out.println("获得Excel表格的内容:");

216 for (int i = 1; i <= map.size(); i++) {217 //System.out.println(map.get(i));

218 sql = map.get(i).values().toString().substring(1,map.get(i).values().toString().length()-1);219 //System.out.println("sql=" + sql);

220 stmt.executeUpdate("insert into tbl_data_bak values("+sql+");");221 }222

223 //插入数据后

224 sql = "select count(*) from tbl_data_bak";225 ret =stmt.executeQuery(sql);226 if(ret.next()) {227 System.out.print("count="+ret.getInt(1));228 }229 }230 stmt.close();231 conn.close();232 }233 /*catch(SQLException e) {234 e.printStackTrace();235 }*/

236 catch(FileNotFoundException e) {237 System.out.println("未找到指定路径的文件!");238 e.printStackTrace();239 } catch(Exception e) {240 e.printStackTrace();241 }242 }243 }