一、Excel理論知識 最新版NPOI2.4.1鏈接：https://pan.baidu.com/s/1iTgJi2hGsRQHyw2S_4dIUw 提取碼：adnq • 整個Excel表格叫做工作簿：WorkBook • 工作簿由以下幾部分組成 a.頁(Sheet); b.行(Row); c.單元 ...
一、Excel理論知識
最新版NPOI2.4.1鏈接：https://pan.baidu.com/s/1iTgJi2hGsRQHyw2S_4dIUw 提取碼：adnq
• 整個Excel表格叫做工作簿：WorkBook
• 工作簿由以下幾部分組成
a.頁(Sheet);
b.行(Row);
c.單元格(Cell);
二、處理Excel的技術
•OLE Automation：程式啟動一個Excel進程，然後和Excel進程進行通訊來運行Excel的操作。
優點：強大，Excel能實現的功能，都可以實現
缺點：必須裝Excel
•把Excel當成資料庫，使用Microsoft.Jet.OleDb訪問Excel，只適合二維結構，功能少，不用裝Excel
•OpenXML，微軟提供的讀寫Excel的技術，只能處理xlsx格式文件
•NPOI、MyXls，能夠分析Excel文件的格式，能夠進行常用Excel操作，不依賴於Excel，節省資源，沒有安全性和性能的問題。只能處理xls格式文件、不能處理xlsx這樣的新版本Excel文件格式。處理xlsx用OpenXML
1 描述工作簿的類：IWorkbook(介面)、HSSFWorkbook(具體實現類) 2 3 描述工作表的類：ISheet(介面)、HSSFSheet(具體實現類)
三、NPOI導出
方式一(預設導出位置)
1 private void button1_Click(object sender, EventArgs e) 2 { 3 List<Person> list = new List<Person>() { 4 new Person(){Name="張三",Age="15",Email="[email protected]" }, 5 new Person(){Name="李四",Age="16",Email="[email protected]" }, 6 new Person(){Name="王五",Age="17",Email="[email protected]" } 7 }; 8 // 引用命名空間 9 // using NPOI.HSSF.UserModel; 10 // using NPOI.SS.UserModel; 11 // using System.IO; 12 //將List集合中的內容導出到Excel中 13 //1、創建工作簿對象 14 IWorkbook wkBook = new HSSFWorkbook(); 15 //2、在該工作簿中創建工作表對象 16 ISheet sheet = wkBook.CreateSheet("人員信息"); //Excel工作表的名稱 17 //2.1向工作表中插入行與單元格 18 for (int i = 0; i < list.Count; i++) 19 { 20 //在Sheet中插入創建一行 21 IRow row = sheet.CreateRow(i); 22 //在該行中創建單元格 23 //方式一 24 //ICell cell = row.CreateCell(0); 25 //cell.SetCellValue(list[i].Name); 26 //方式二 27 row.CreateCell(0).SetCellValue(list[i].Name); //給單元格設置值：第一個參數(第幾個單元格)；第二個參數(給當前單元格賦值) 28 row.CreateCell(1).SetCellValue(list[i].Age); 29 row.CreateCell(2).SetCellValue(list[i].Email); 30 } 31 //3、寫入，把記憶體中的workBook對象寫入到磁碟上 32 FileStream fsWrite = File.OpenWrite("Person.xls"); //導出時Excel的文件名 33 wkBook.Write(fsWrite); 34 MessageBox.Show("寫入成功！", "提示"); 35 fsWrite.Close(); //關閉文件流 36 wkBook.Close(); //關閉工作簿 37 fsWrite.Dispose(); //釋放文件流 38 39 }
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace _01NPOI的寫入 8 { 9 public class Person 10 { 11 public string Name { get; set; } 12 public string Age { get; set; } 13 public string Email { get; set; } 14 } 15 }Person類
方式二(更友好的一種方式，用戶可以指定導出位置)推薦
1 private void button3_Click(object sender, EventArgs e) 2 { 3 List<Person> list = new List<Person>() { 4 new Person(){Name="張三",Age="15",Email="[email protected]" }, 5 new Person(){Name="李四",Age="16",Email="[email protected]" }, 6 new Person(){Name="王五",Age="17",Email="[email protected]" } 7 }; 8 //創建文件 9 string fileName = "人員信息表"; 10 string saveFilePath = ""; //導出時文件的路徑 11 SaveFileDialog saveDialog = new SaveFileDialog(); 12 saveDialog.DefaultExt = "xls"; //預設文件擴展名 13 saveDialog.Filter = "Excel文件|*.xls"; //文件名篩選字元串 14 saveDialog.FileName = fileName; //導出文件名稱 15 saveDialog.ShowDialog(); //顯示視窗 16 saveFilePath = saveDialog.FileName; //文件路徑 17 // 引用命名空間 18 // using NPOI.HSSF.UserModel; 19 // using NPOI.SS.UserModel; 20 // using System.IO; 21 //將List集合中的內容導出到Excel中 22 //1、創建工作簿對象 23 IWorkbook wkBook = new HSSFWorkbook(); 24 //2、在該工作簿中創建工作表對象 25 ISheet sheet = wkBook.CreateSheet("人員信息"); //Excel工作表的名稱 26 //2.1向工作表中插入行與單元格 27 for (int i = 0; i < list.Count; i++) 28 { 29 //在Sheet中插入創建一行 30 IRow row = sheet.CreateRow(i); 31 //在該行中創建單元格 32 //方式一 33 //ICell cell = row.CreateCell(0); 34 //cell.SetCellValue(list[i].Name); 35 //方式二 36 row.CreateCell(0).SetCellValue(list[i].Name); //給單元格設置值：第一個參數(第幾個單元格)；第二個參數(給當前單元格賦值) 37 row.CreateCell(1).SetCellValue(list[i].Age); 38 row.CreateCell(2).SetCellValue(list[i].Email); 39 } 40 //3、寫入，把記憶體中的workBook對象寫入到磁碟上 41 FileStream fsWrite = new FileStream(saveFilePath,FileMode.Create); 42 wkBook.Write(fsWrite); 43 MessageBox.Show("寫入成功！", "提示"); 44 fsWrite.Close(); //關閉文件流 45 wkBook.Close(); //關閉工作簿 46 fsWrite.Dispose(); //釋放文件流 47 }
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace _01NPOI的寫入 8 { 9 public class Person 10 { 11 public string Name { get; set; } 12 public string Age { get; set; } 13 public string Email { get; set; } 14 } 15 }Person類
方式三、導出dataGridView數據
1 public static void ExportExcel(string fileName, DataGridView dgv) 2 { 3 string saveFileName = ""; 4 SaveFileDialog saveDialog = new SaveFileDialog(); 5 saveDialog.DefaultExt = "xls"; 6 saveDialog.Filter = "Excel文件|*.xls"; 7 saveDialog.FileName = fileName; 8 saveDialog.ShowDialog(); 9 saveFileName = saveDialog.FileName; 10 11 HSSFWorkbook workbook = new HSSFWorkbook(); 12 MemoryStream ms = new MemoryStream(); 13 14 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1"); 15 16 int rowCount = dgv.Rows.Count+1; 17 int colCount = dgv.Columns.Count; 18 int r1; 19 NPOI.SS.UserModel.IRow dataRow1 = sheet.CreateRow(0); 20 21 for (int i = 0; i < rowCount; i++) 22 { 23 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(i); 24 for (int j = 1; j < colCount; j++) 25 { 26 if (i == 0) 27 { 28 r1 = i; 29 } 30 else 31 { 32 r1 = i - 1; 33 } 34 if (dgv.Columns[j].Visible && dgv.Rows[r1].Cells[j].Value != null) 35 { 36 NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j-1); 37 if (i == 0) 38 { 39 cell.SetCellValue(dgv.Columns[j].HeaderCell.Value.ToString()); 40 continue; 41 } 42 cell.SetCellValue(dgv.Rows[r1].Cells[j].FormattedValue.ToString()); 43 } 44 else 45 { 46 NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j-1); 47 cell.SetCellValue(""); 48 } 49 } 50 } 51 52 workbook.Write(ms); 53 FileStream file = new FileStream(saveFileName, FileMode.Create); 54 workbook.Write(file); 55 file.Close(); 56 workbook = null; 57 ms.Close(); 58 ms.Dispose(); 59 }DGV中數據導出Excel
四、NPOI讀取Excel內容
1 private void button2_Click(object sender, EventArgs e) 2 { 3 //需要讀取的文件:人員表.xls 4 // 創建文件 5 OpenFileDialog ofd = new OpenFileDialog(); 6 ofd.Filter = "Excel文件|*.xls"; 7 ofd.ShowDialog(); 8 string filePath = ofd.FileName; 9 FileStream fsRead=null; 10 IWorkbook wkBook = null; 11 if (filePath != "") 12 { 13 //1、創建一個工作簿workBook對象 14 fsRead = new FileStream(filePath, FileMode.Open); 15 //將人員表.xls中的內容讀取到fsRead中 16 wkBook = new HSSFWorkbook(fsRead); 17 //2、遍歷wkBook中的每個工作表Sheet 18 for (int i = 0; i < wkBook.NumberOfSheets; i++) 19 { 20 //獲取每個工作表對象 21 ISheet sheet = wkBook.GetSheetAt(i); 22 //獲取每個工作表的行 23 //foreach遍歷 sheet.GetEnumerator 24 for (int r = 0; r < sheet.LastRowNum; r++) 25 { 26 //獲取工作表中的每一行 27 IRow currentRow = sheet.GetRow(r); 28 //遍歷當前行中的每個單元格 29 for (int c = 0; c < currentRow.LastCellNum; c++) 30 { 31 try 32 { 33 //獲取每個單元格 34 ICell cell = currentRow.GetCell(c); 35 if (cell == null) //如果單元格為空時，程式會報錯，這裡判斷提示用戶，用try catch防止程式蹦 36 { 37 MessageBox.Show(string.Format("第{0}行,第{1}列單元格為空！",r,c)); 38 } 39 CellType cType = cell.CellType; // 獲取單元格中的類型 40 MessageBox.Show(cType.ToString()); 41 //判斷當前單元格的數據類型，可以拓展 42 switch (cType) 43 { 44 case CellType.Numeric: //數字 45 MessageBox.Show("我是數字"); 46 break; 47 case CellType.String: //字元串 48 MessageBox.Show("我是字元串"); 49 break; 50 case CellType.Boolean: 51 MessageBox.Show("我是布爾值"); 52 break; 53 } 54 //獲取單元格的值 55 //日期 56 DateTime date = cell.DateCellValue; 57 //數字 58 double num = cell.NumericCellValue; 59 //字元串 60 string str = cell.StringCellValue; 61 //布爾值 62 bool bl = cell.BooleanCellValue; 63 } 64 catch (Exception EX) 65 { 66 67 } 68 69 } 70 } 71 } 72 } 73 else 74 { 75 MessageBox.Show("選擇文件失敗!","提示"); 76 } 77 fsRead.Close(); 78 wkBook.Close(); 79 fsRead.Dispose(); 80 81 }
五、資料庫中數據，導出Excel
private void button4_Click(object sender, EventArgs e) { // 需引用命名空間 // using System.Data.SqlClient; // using NPOI.HSSF.UserModel; // using NPOI.SS.UserModel; // using System.IO; //1、通過ado.net讀取數據 string strSql = "SELECT * FROM Students"; SqlDataReader reader = sqlHelper.ExecuteReader(strSql,CommandType.Text); if (reader.HasRows) //若有數據 { //2、將讀取到的數據寫入到Excel中 //2.1創建工作簿WorkBook對象 IWorkbook wkBook = new HSSFWorkbook(); //2.2創建工作表 ISheet sheet = wkBook.CreateSheet("人員信息表"); //工作表名稱 int rIndex = 0; while (reader.Read()) { //每讀取一條數據，就創建一行row IRow currentRow = sheet.CreateRow(rIndex); rIndex++; int ID = reader.GetInt32(0); string name = reader.GetString(1); int age = reader.GetInt32(2); //向行中創建單元格 currentRow.CreateCell(0).SetCellValue(ID); //第一個參數：單元格索引；第二個參數：給單元格賦值 currentRow.CreateCell(1).SetCellValue(name); currentRow.CreateCell(2).SetCellValue(age); } //創建文件 string fileName = "人員信息表"; string saveFilePath = ""; //導出時文件的路徑 SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; //預設文件擴展名 saveDialog.Filter = "Excel文件|*.xls"; //文件名篩選字元串 saveDialog.FileName = fileName; //導出文件名稱 saveDialog.ShowDialog(); //顯示視窗 saveFilePath = saveDialog.FileName; //文件路徑 //將workBook對象寫入到磁碟上 FileStream fsWrite = new FileStream(saveFilePath, FileMode.Create); wkBook.Write(fsWrite); MessageBox.Show("數據導出成功！", "提示"); fsWrite.Close(); //關閉文件流 wkBook.Close(); //關閉工作簿 fsWrite.Dispose(); //釋放文件流 } else { MessageBox.Show("沒有數據"); } //reader.Close(); }
1 public static SqlDataReader ExecuteReader(string strSql, CommandType cmdType, params SqlParameter[] pms) 2 { 3 SqlDataReader sr = null; 4 SqlConnection conn = new SqlConnection(conStr); 5 SqlCommand cmd = new SqlCommand(strSql, conn); 6 cmd.CommandType = cmdType; 7 if (pms != null) 8 { 9 cmd.Parameters.AddRange(pms); 10 } 11 try 12 { 13 if (conn.State == ConnectionState.Closed) 14 { 15 conn.Open(); 16 } 17 sr = cmd.ExecuteReader(); 18 return sr; 19 } 20 catch (Exception EX) 21 { 22 MessageBox.Show(EX.Message.ToString()); 23 } 24 finally 25 { 26 cmd.Dispose(); 27 } 28 return sr; 29 }函數
六、Excel數據導入資料庫
資料庫欄位
Excel數據(必須和資料庫欄位對上)
1 Thread th; //聲明公共變數 2 private void button5_Click(object sender, EventArgs e) 3 { 4 //因為要遍歷Excel中的數據，我們這裡用線程執行 5 // 需引入命名空間 6 //using System.Threading; 7 //using System.Data.SqlClient; 8 //using NPOI.HSSF.UserModel; 9 //using NPOI.SS.UserModel; 10 //using System.IO; 11 //創建文件 12 object filePath = ""; // 文件路徑 13 OpenFileDialog ofd = new OpenFileDialog(); //創建文件 14 ofd.Filter = "Excel文件|*.xls"; 15 ofd.ShowDialog(); 16 filePath = ofd.FileName; 17 th = new Thread(inportData); 18 th.IsBackground = true; //將線程設置為後臺進程 19 th.Start(filePath); 20 ofd.Dispose(); 21 } 22 private void inportData(object filePath) 23 { 24 // 創建表副本 SELECT TOP 0 * INSERT INTO newTable FROM oldTable 25 //1、從Excel中讀取數據 26 if (filePath.ToString() != "") 27 { 28 FileStream fsRead = new FileStream(filePath.ToString(), FileMode.Open); 29 //一、創建工作簿 30 IWorkbook workBook = new HSSFWorkbook(fsRead); 31 string insert_sql = ""; 32 string insert_module = "INSERT INTO Student2 (id,name,age) VALUES ({0})"; 33 StringBuilder sb = new StringBuilder(); 34 for (int i = 0; i < workBook.NumberOfSheets; i++) 35 { 36 //獲取工作表 37 ISheet sheet = workBook.GetSheetAt(i); 38 for (int r = 0; r <= sheet.LastRowNum; r++) //遍歷當前工作表中的所有行 39 { 40 IRow currentRow = sheet.GetRow(r); //獲取每一行 41 for (int c = 0; c < currentRow.LastCellNum; c++) //遍歷當前行中的所有列 42 { 43 //獲取每個單元格 44 ICell cell = currentRow.GetCell(c); 45 //listCells.Add(cell); 46 sb.Append("'").Append(cell.ToString()).Append("',"); 47 } 48 //拼接SQL語句 49 insert_sql += string.Format(insert_module, sb.ToString().Substring(0, sb.ToString().Length - 1)) + ";"; 50 sb.Clear(); 51 //
