public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int startRowIndex) { System.Data.DataTable dataSource = new System.Data.DataTable(); dataSource.Columns.Add("Index", typeof(string)); dataSource.Columns.Add("IndexNPOI", typeof(string)); dataSource.Columns.Add("Name", typeof(string)); dataSource.Columns.Add("Description", typeof(string)); dataSource.Columns.Add("HexString", typeof(string)); // 获取Workbook。 NPOI.SS.UserModel.IWorkbook workbook = NPOIExtension.GetWorkbookFromExcelFile(fileName); if (workbook == null) { return(dataSource); } // 获取Sheet NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); for (int i = startRowIndex; i < sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); System.Data.DataRow dataSourceRow = dataSource.NewRow(); dataSource.Rows.Add(dataSourceRow); for (int j = 0; j < dataSource.Columns.Count; j++) { dataSourceRow[j] = row.GetCell(j).ToString(); } } return(dataSource); }
public static void CreateExcelColorFile(string fileName) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName); // 创建Workbook。 NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook(); if (fileInfo.Extension == ".xlsx") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else if (fileInfo.Extension == ".xls") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { return; } // 创建Sheet NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor"); // 设置列宽。 sheet.SetColumnWidth(0, 15 * 256); sheet.SetColumnWidth(1, 15 * 256); sheet.SetColumnWidth(2, 20 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 15 * 256); // 创建标题。 int rowIndex = 0; NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0); cell.SetCellValue("Excel颜色"); cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold)); // 空一行 rowIndex++; // 创建标题行 rowIndex++; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); row.Height = 30 * 20; NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center); cell = row.CreateCell(0); cell.SetCellValue("索引"); cell.CellStyle = columnHeadCellStyle; cell = row.CreateCell(1); cell.SetCellValue("索引(NPOI)"); cell.CellStyle = columnHeadCellStyle; cell = row.CreateCell(2); cell.SetCellValue("名称"); cell.CellStyle = columnHeadCellStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3)); cell = row.CreateCell(4); cell.SetCellValue("十六进制代码"); cell.CellStyle = columnHeadCellStyle; ExcelColor.KnownColors.ForEach(x => { rowIndex++; NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex); contentRow.Height = 30 * 20; NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center); NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0); contentCell.SetCellValue(x.Index); contentCell.CellStyle = contentCellStyle; contentCell = contentRow.CreateCell(1); contentCell.SetCellValue(x.IndexNPOI + "(NPOI)"); contentCell.CellStyle = contentCellStyle; contentCell = contentRow.CreateCell(2); contentCell.SetCellValue(x.Name); contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center); contentCell = contentRow.CreateCell(3); contentCell.SetCellValue(x.Description); contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center); contentCell = contentRow.CreateCell(4); contentCell.SetCellValue(x.HexString); contentCell.CellStyle = contentCellStyle; }); // 保存到文件。 System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite); workbook.Write(fileStream); fileStream.Close(); workbook.Close(); }
private static void NPOIDemo2() { //NPOIDemo.CreateExcelColorFile("ExcelColor.xlsx"); System.Data.DataTable dataSource = NPOIExtension.GetDataTabelFromExcelFile("Test.xlsx", true); }