C#通過NPOI導入導出數據EXCEL
本文實例為大傢分享瞭C#通過NPOI導入導出數據EXCEL的具體代碼,供大傢參考,具體內容如下
其實從數據庫到服務器導入導出有很多方法,但是比較簡單的有NPOI這個方法
準備工作,先增加到相應的lib,同時引用相關的dll
第一從數據庫導出數據到Excel裡面
首先我設置一個win窗體
第二步寫代碼
private void button1_Click(object sender, EventArgs e) { //查詢數據,顯示到表格上 ManagerInfoBll miBll = new ManagerInfoBll(); var list = miBll.GetList(); dataGridView1.DataSource = list; //進行excel 生成創建操作 //1 創建workboook ,不指定參數,表示創建一個新的工作本 HSSFWorkbook workbook = new HSSFWorkbook(); //2創建sheet HSSFSheet sheet = workbook.CreateSheet("管理員"); //3創建row HSSFRow row = sheet.CreateRow(0); //4創建cell HSSFCell cell0 = row.CreateCell(0); cell0.SetCellValue("管理員表"); //5設置合並單元格 sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 3)); //6設置居中 HSSFCellStyle styleTitle = workbook.CreateCellStyle(); styleTitle.Alignment = 2; cell0.CellStyle = styleTitle; //6.1設置字體 HSSFFont fontTitle = workbook.CreateFont(); fontTitle.FontHeightInPoints = 14; styleTitle.SetFont(fontTitle); //7創建標題行 //7.1創建行 HSSFRow rowTitle = sheet.CreateRow(1); //7.2創建單元格 HSSFCell cellTitle0 = rowTitle.CreateCell(0); cellTitle0.SetCellValue("編號"); cellTitle0.CellStyle = styleTitle; HSSFCell cellTitle1 = rowTitle.CreateCell(1); cellTitle1.SetCellValue("姓名"); cellTitle1.CellStyle = styleTitle; HSSFCell cellTitle2 = rowTitle.CreateCell(2); cellTitle2.SetCellValue("密碼"); cellTitle2.CellStyle = styleTitle; HSSFCell cellTitle3 = rowTitle.CreateCell(3); cellTitle3.SetCellValue("類型"); cellTitle3.CellStyle = styleTitle; int rowIndex = 2; //8.1遍歷集合 foreach (var mi in list) {//8.2創建行 HSSFRow rowData = sheet.CreateRow(rowIndex++); //8.3創建單元格 HSSFCell cellData0 = rowData.CreateCell(0); cellData0.SetCellValue(mi.MId); HSSFCell cellData1 = rowData.CreateCell(1); cellData1.SetCellValue(mi.MName); HSSFCell cellData2 = rowData.CreateCell(2); cellData2.SetCellValue(mi.MPwd); HSSFCell cellData3 = rowData.CreateCell(3); cellData0.SetCellValue(mi.MType==1?"經理":"店員"); } //8 保持文本 FileStream stream = new FileStream("", FileMode.Create); workbook.Write(stream);// stream.Close(); stream.Dispose(); }
最後效果
第二從Excel導入到數據庫中
private void button2_Click(object sender, EventArgs e) { //1讀取excel 數據,存入list List<ManagerInfo> list = new List<ManagerInfo>(); //1.1讀取文件 路徑 using (FileStream stream= new FileStream("",FileMode.Open)) { //1.2創建workbook HSSFWorkbook workbook = new HSSFWorkbook(stream); //1.3讀取sheet HSSFSheet sheet = workbook.GetSheetAt(0); //1.4讀取正文數據主要從表格開始看起 第三行開始 int rowindex = 2; HSSFRow row = sheet.GetRow(10); while ( row==null) { //1.5讀到數據返回行對象 ManagerInfo mi = new ManagerInfo(); mi.MId = (int)row.GetCell(0).NumericCellValue; mi.MName = row.GetCell(1).StringCellValue; mi.MPwd = row.GetCell(2).StringCellValue; mi.MType=row.GetCell(3).StringCellValue=="經理"?1 : 0; list.Add(mi); row = sheet.GetRow(rowindex++); } } //2將list賦值給dataGridview dataGridView1.DataSource = list; }
以上就是本文的全部內容,希望對大傢的學習有所幫助,也希望大傢多多支持WalkonNet。
推薦閱讀:
- 教你如何使用JAVA POI
- C#使用NPOI對Excel數據進行導入導出
- C#使用Npoi導出Excel並合並行列
- Java操作Excel文件解析與讀寫方法詳解
- C#利用NPOI操作Excel(單元格設置)