将exce中数据导入数据库表

创建excel表格，从中获取数据，然后将数据放到mysql表中
之前有点搞不懂如何将数据放入数据中，所以在网上找了很多资料，发现都没办法完全理解
于是跟同学拿了代码看，终于找到最简单的方法:-D 反正我是搞懂了
希望下次可以自己研究出来吧/(ㄒoㄒ)/~~





1 /**
  2  * Created by Min on 2016/3/22.
  3  */
  4 
  5 
  6 import org.apache.poi.hssf.usermodel.HSSFCell;
  7 import org.apache.poi.hssf.usermodel.HSSFRow;
  8 import org.apache.poi.hssf.usermodel.HSSFSheet;
  9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 10 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 11 import java.io.FileInputStream;
 12 import java.io.FileOutputStream;
 13 import java.sql.Connection;
 14 import java.sql.DriverManager;
 15 import java.sql.Statement;
 16 
 17 /**
 18  * 同时在数据库和Excel中，建立包含编号(code),姓名(name), 密码(password),性别 (gender),邮箱(email)
 19  * 等字段的表格，并在Excel中建立数据(三条记录以上)。编写程序从Excel中将数据读出，并写到相应的数据库表格中
 20  *
 21  * @author Administrator
 22  *
 23  */
 24 public class todatabase {
 25 
 26     public static void main(String[] args) {
 27 
 28         //实例化
 29         todatabase work = new todatabase();
 30         work.createExl();
 31         work.save();
 32     }
 33 
 34     /**
 35      * 生成exl文件
 36      */
 37     public void createExl() {
 38 
 39         // 定义存放文件的目录
 40         String outputFile = "D:\\1.xls";
 41 
 42         try {
 43             // 创建工作簿
 44             HSSFWorkbook workbook = new HSSFWorkbook();
 45             HSSFSheet sheet = workbook.createSheet();
 46 
 47             // 在索引1的位置建行(最顶端的下一行)
 48             HSSFRow row = sheet.createRow((short) 0);
 49             // 在索引0的位置创建单元格（左上端）
 50             HSSFCell cell = row.createCell((short) 0);
 51             // 定义单元格字符串类型
 52             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 53             // 设置cell编码解决中文高位字节截断
 54             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 55             // 在单元格中输入一些内容X
 56             int i = 0;
 57             cell = row.createCell((short) i++);
 58             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 59             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 60             cell.setCellValue("编号(code)");
 61 
 62             cell = row.createCell((short) i++);
 63             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 64             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 65             cell.setCellValue("姓名(name)");
 66 
 67             cell = row.createCell((short) i++);
 68             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 69             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 70             cell.setCellValue("密码(password)");
 71 
 72             cell = row.createCell((short) i++);
 73             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 74             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 75             cell.setCellValue("性别 (gender)");
 76 
 77             cell = row.createCell((short) i++);
 78             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 79             cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 80             cell.setCellValue("邮箱(email)");
 81 
 82             // 填充数据
 83             for (int j = 0; j < 3; j++) {
 84                 i = 0;
 85                 row = sheet.createRow((short) j + 1);
 86                 // 编号
 87                 cell = row.createCell((short) i++);
 88                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 89                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 90                 cell.setCellValue("dd"+j);
 91 
 92                 // 姓名
 93                 cell = row.createCell((short) i++);
 94                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 95                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
 96                 cell.setCellValue("cc" + j);
 97 
 98                 // 密码
 99                 cell = row.createCell((short) i++);
100                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
101                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
102                 cell.setCellValue("111" + j);
103 
104                 // 性别
105                 cell = row.createCell((short) i++);
106                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
107                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
108                 cell.setCellValue("男");
109 
110                 // 邮箱(email)
111                 cell = row.createCell((short) i++);
112                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
113                 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
114                 cell.setCellValue(j + "11@qq.com");
115             }
116 
117             // 写出文件
118             FileOutputStream fOut = new FileOutputStream(outputFile);
119             workbook.write(fOut);
120             fOut.flush();
121             fOut.close();
122             System.out.println("文件生成");
123         } catch (Exception e) {
124             System.out.println(e);
125         }
126 
127     }
128 
129     /**
130      * 数据库操作
131      */
132     public void save(){
133         try {
134 
135             //读文件
136             POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\1.xls"));
137             HSSFWorkbook wb = new HSSFWorkbook(fs);
138             HSSFSheet sheet2 = wb.getSheetAt(0);
139 
140             //数据库连接信息
141             String driver = "com.mysql.jdbc.Driver";
142             String url = "jdbc:mysql://localhost:3306/fordata?"+"useUnicode=true&characterEncoding=utf-8";
143             String user = "root";
144             String password = "123456";
145             Class.forName(driver);
146             // 打开JDBC数据源edu
147             Connection conn = DriverManager.getConnection(url, user, password);
148             Statement stmt = conn.createStatement();
149 
150             //取数据
151             for (int j = 0; j < sheet2.getLastRowNum(); j++) {
152                 HSSFRow row2 = sheet2.getRow(j + 1);
153                 HSSFCell cell0 = row2.getCell((short) 0);
154                 HSSFCell cell1 = row2.getCell((short) 1);
155                 HSSFCell cell2 = row2.getCell((short) 2);
156                 HSSFCell cell3 = row2.getCell((short) 3);
157                 HSSFCell cell4 = row2.getCell((short) 4);
158 
159                 String code = cell0.getStringCellValue();
160                 String name = cell1.getStringCellValue();
161                 String passwd = cell2.getStringCellValue();
162                 String sex = cell3.getStringCellValue();
163                 String email = cell4.getStringCellValue();
164 
165                 // 插入值到数据库表
166                 stmt.execute("INSERT INTO term2_work VALUES(‘" + code + "‘ ,‘"
167                         + name + "‘,‘" + passwd + "‘,‘" + sex + "‘,‘" + email
168                         + "‘)");
169 
170             }
171 
172             //关闭
173             stmt.close();
174             conn.close();
175         } catch (Exception e) {
176             // TODO Auto-generated catch block
177             e.printStackTrace();
178         }
179     }
180     // public static void main(String[] args) {
181     // Work work = new Work();
182     // work.getCreateExcel();
183     // }
184     //
185     // public void getCreateExcel(){
186     // //获取当前时间，用做定义保存文件名称
187     // SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
188     // String format = date.format(new Date(System.currentTimeMillis()));
189     //
190     // //定义存放文件的目录
191     // String outputFile = "F:/a/"+format.replace(":", "")+".xls";
192     //
193     // try {
194     // //创建工作簿
195     // HSSFWorkbook workbook = new HSSFWorkbook();
196     // HSSFSheet sheet = workbook.createSheet();
197     // this.getTitleCellStyle(workbook,sheet);
198     // //在索引1的位置建行(最顶端的下一行)
199     // HSSFRow row = sheet.createRow((short)1);
200     // //在索引0的位置创建单元格（左上端）
201     // HSSFCell cell = row.createCell((short)0);
202     // //定义单元格字符串类型
203     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
204     // //设置cell编码解决中文高位字节截断
205     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
206     // //在单元格中输入一些内容X
207     // int i=0;
208     // cell = row.createCell((short)i++);
209     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
210     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
211     // cell.setCellValue("教师id");
212     //
213     // cell = row.createCell((short)i++);
214     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
215     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
216     // cell.setCellValue("教师姓名");
217     //
218     // cell = row.createCell((short)i++);
219     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
220     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
221     // cell.setCellValue("登录密码");
222     //
223     // cell = row.createCell((short)i++);
224     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
225     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
226     // cell.setCellValue("性别");
227     //
228     // cell = row.createCell((short)i++);
229     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
230     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
231     // cell.setCellValue("联系电话");
232     //
233     // //填充数据
234     // for (int j = 0; j < 5; j++) {
235     // i = 0;
236     // row = sheet.createRow((short)j + 2);
237     // cell = row.createCell((short) i++);
238     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
239     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
240     // cell.setCellStyle(this.getGenCellStyle(workbook));
241     // cell.setCellValue("time:"+format);
242     //
243     // cell = row.createCell((short) i++);
244     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
245     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
246     // cell.setCellStyle(this.getGenCellStyle(workbook));
247     // cell.setCellValue("j:"+j);
248     //
249     // cell = row.createCell((short) i++);
250     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
251     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
252     // cell.setCellStyle(this.getGenCellStyle(workbook));
253     // cell.setCellValue("j:"+j);
254     //
255     // cell = row.createCell((short) i++);
256     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
257     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
258     // cell.setCellStyle(this.getGenCellStyle(workbook));
259     // cell.setCellValue("j:"+j);
260     //
261     // cell = row.createCell((short) i++);
262     // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
263     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
264     // cell.setCellStyle(this.getGenCellStyle(workbook));
265     // cell.setCellValue("j:"+j);
266     // }
267     //
268     // //写出文件
269     // FileOutputStream fOut = new FileOutputStream(outputFile);
270     // workbook.write(fOut);
271     // fOut.flush();
272     // fOut.close();
273     // System.out.println("文件生成");
274     // } catch (Exception e) {
275     // System.out.println(e);
276     // }
277     // }
278     //
279     //
280     // //定义获取日期的格式的类型
281     // public HSSFCellStyle getDateCellStyle(HSSFWorkbook workbook){
282     // HSSFFont font = workbook.createFont();
283     // font.setFontHeightInPoints((short)12);
284     // font.setFontName("宋体");
285     // HSSFCellStyle cellDateStyle = workbook.createCellStyle();
286     // cellDateStyle.setFont(font);
287     // cellDateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd HH:mm:ss"));
288     // return cellDateStyle;
289     //
290     // }
291     //
292     // //定义普通类型的列样式
293     // public HSSFCellStyle getGenCellStyle(HSSFWorkbook workbook){
294     // HSSFCellStyle cellStyle = workbook.createCellStyle();
295     // cellStyle.setBorderBottom((short) 2);
296     // cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
297     // cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
298     // cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
299     // cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
300     // return cellStyle;
301     // }
302     //
303     //
304     // //定义表头信息的样式，在索引0的位置开始
305     // public HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook,HSSFSheet
306     // sheet){
307     // HSSFRow row = sheet.createRow((short)0);
308     // //合并单元格，参数分别在0行0列的位置合并
309     // sheet.addMergedRegion(new
310     // org.apache.poi.hssf.util.Region(0,(short)0,0,(short)4));
311     // //设置字体
312     // HSSFFont font = workbook.createFont();
313     // font.setFontHeight((short) 20);
314     // font.setFontHeightInPoints((short) 20);
315     // HSSFCellStyle titleCellStyle = workbook.createCellStyle();
316     //
317     // //文字居中
318     // titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
319     // titleCellStyle.setFont(font);
320     // HSSFCell cell = row.createCell((short) 0);
321     // cell.setCellStyle(titleCellStyle);
322     // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
323     // cell.setCellValue("教师名录");
324     // return titleCellStyle;
325     // }
326 
327 }