首页 > 代码库 > NPOI将Excel中的数据导入到数据库
NPOI将Excel中的数据导入到数据库
using(FileStream fs=File.OpenRead(@"d:/MY.xls")) { IWorkbook wk = new HSSFWorkbook(fs); if (wk.NumberOfSheets>0) { ISheet sheet = wk.GetSheetAt(0); for (int i = 0; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); // //Id, Name, ParentID //string name=row.GetCell(1).CellType==CellType.STRING.StringCellValue; string Name = row.GetCell(1).StringCellValue =http://www.mamicode.com/="null" ? null : row.GetCell(1).StringCellValue; int? ParentID = row.GetCell(2) == null ? null:(int?) row.GetCell(2).NumericCellValue; string sql = "insert into TreeView values( @Name, @ParentID)"; SqlParameter[] pms = new SqlParameter[]{ new SqlParameter("@Name",Name==null?DBNull.Value:(object)Name), new SqlParameter("@ParentID",ParentID==null?DBNull.Value:(object)ParentID) }; SqlHelper.ExecteNonQuery(System.Data.CommandType.Text, sql, pms); } } }
public class DoExcel { public static void LogeToExcel(string[] strs) { DateTime dt = DateTime.Now; string dir = "/ApiLogs/" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; string path = HttpContext.Current.Server.MapPath(dir); int rowNum = 0; if (!File.Exists(path)) { //Directory.CreateDirectory(path); //创建一个workbook IWorkbook wk = new HSSFWorkbook(); //创建sheet ISheet sh = wk.CreateSheet("Sheet1"); IRow row = sh.CreateRow(rowNum); for (int i = 0; i < strs.Length; i++) { ICell iCell = row.CreateCell(i); iCell.SetCellValue(strs[i]); } using (FileStream fs = File.OpenWrite(path)) { wk.Write(fs); } } else { FileStream fss = File.OpenRead(path); IWorkbook wk = new HSSFWorkbook(fss); int InsertRowIndex = wk.GetSheetAt(0).LastRowNum+1; ExportExcelByTemple(strs, path, path, 20, InsertRowIndex); } } /// <summary> /// 用模板导出Excel /// </summary> /// <param name="table"></param> /// <param name="strFileName">导出路径</param> /// <param name="templetPath">模板路径</param> /// <param name="startRow">从第几行开始写数据,从1开始</param> public static void ExportExcelByTemple(string[] strs, string strFileName, string templetPath, int rowHeight, int startRow) { try { HSSFWorkbook workbook = getWorkBook(templetPath); ISheet sheet = getSheet(workbook); writeData(workbook, sheet, strs, strFileName, rowHeight, startRow); saveData(workbook, strFileName); } catch (Exception ex) { throw ex; } } /// <summary> /// 解析Excel模板，返回WorkBook /// </summary> /// <param name="templetPath"></param> /// <returns></returns> private static HSSFWorkbook getWorkBook(string templetPath) { FileStream file = new FileStream(templetPath, FileMode.Open, FileAccess.Read); HSSFWorkbook workbook = new HSSFWorkbook(file); return workbook; } /// <summary> /// 返回Sheet /// </summary> /// <param name="workbook"></param> /// <returns></returns> private static ISheet getSheet(HSSFWorkbook workbook) { return workbook.GetSheetAt(0); } /// <summary> /// /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="dtSource"></param> /// <param name="strFileName"></param> /// <param name="rowHeight"></param> /// <param name="startRow"></param> /// <param name="size"></param> private static void writeData(HSSFWorkbook workbook, ISheet sheet, string[] strs, string strFileName, int rowHeight, int startRow) { // //填充表头 IRow dataRow = new HSSFRow(); //填充内容 dataRow = sheet.CreateRow(startRow); dataRow.Height = (short)(rowHeight * 20); for (int j = 0; j < strs.Length; j++) { string drValue =http://www.mamicode.com/ strs[j]; dataRow.CreateCell(j).SetCellValue(drValue); } } /// <summary> /// 保存数据 /// </summary> /// <param name="workbook"></param> /// <param name="strFileName"></param> private static void saveData(HSSFWorkbook workbook, string strFileName) { //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } } }
NPOI将Excel中的数据导入到数据库
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。