如何打开EXCEL，并设置单元格格式

[fzlrpsun]

需求：在后台打开，并且能够设置单元格的格式，如颜色，然后保存
文件比较多，需要效率，请问用什么方法

还有一个问题
Dim wb As New Workbook
报错
检索 COM 类工厂中 CLSID 为 {00020819-0000-0000-C000-000000000046} 的组件失败，原因是出现以下错误: 80040154 没有注册类 (异常来自 HRESULT:0x80040154 (REGDB_E_CLASSNOTREG))。


谢谢大家

[bdmh]

用npio,感觉还好用

[fzlrpsun]

引用 1 楼 bdmh 的回复:

用npio,感觉还好用

Dim hssfworkbook As HSSFWorkbook = New HSSFWorkbook(fs) fs.Close() Dim sh As HSSFSheet = hssfworkbook.GetSheetAt(0) Dim cell As HSSFCell = sh.GetRow(1).GetCell(1) Dim cellstyle As HSSFCellStyle = hssfworkbook.CreateCellStyle cellstyle.FillBackgroundColor = HSSFColor.Red.Index cell.CellStyle = cellstyle Dim fs1 As New FileStream("test.xls", FileMode.Create) hssfworkbook.Write(fs1) fs1.Close() 请问这个颜色为什么没有设置上

[fzlrpsun]

@bdmh @Z65443344 请教为什么值能设置成功，格式设置不了， 用的是官方的方法 Dim hssfworkbook As HSSFWorkbook = New HSSFWorkbook(fs) fs.Close() Dim sh As HSSFSheet = hssfworkbook.GetSheetAt(0) Dim cell As HSSFCell = sh.GetRow(1).GetCell(1) Dim style As HSSFCellStyle = hssfworkbook.CreateCellStyle cell = sh.CreateRow(8).CreateCell(8) cell.SetCellValue(18) style.FillForegroundColor = HSSFColor.Red.Index style.BottomBorderColor = HSSFColor.Black.Index style.LeftBorderColor = HSSFColor.Green.Index style.RightBorderColor = HSSFColor.Blue.Index style.FillBackgroundColor = HSSFColor.Red.Index style.TopBorderColor = HSSFColor.Orange.Index cell.CellStyle = style Dim fs1 As New FileStream("test.xls", FileMode.Create) hssfworkbook.Write(fs1) fs1.Close()

[zenghongqing]

尽量使用NPOI，这个很容易实现，NPOI是开源的，去官网下载，有很多例子

[fzlrpsun]

求助，很急。。。。

[ee_2499021096]

利用EXCEL将数据生成图表格式 private void tsbtn_Build_Click(object sender, EventArgs e) { CloseProcess("EXCEL");//关闭所有Excel进程 object missing = System.Reflection.Missing.Value;//定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(tstxt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[tscbox_Sheet.Text]);//获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range("A1", "E1");//定义标题范围 object[] P_obj_Items = { "学生词典", "工程词典", "工具词典", "外语词典", "商业词典" }; searchRange.set_Value(missing, P_obj_Items);//绘制标题 searchRange.Font.Bold = true;//设置字体加粗 searchRange.Font.Name = "宋体";//设置字体样式 searchRange.Font.Size = 10;//设置字体大小 searchRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//设置标题对齐方式 //codego.net获得要生成图表的数据 for (int i = 0; i < 13; i++) { worksheet.Cells[2 + i, 1] = i; worksheet.Cells[2 + i, 2] = i + 1; worksheet.Cells[2 + i, 3] = i + 2; worksheet.Cells[2 + i, 4] = i + 3; worksheet.Cells[2 + i, 5] = i + 4; } //实例化Excel绘图对象 Microsoft.Office.Interop.Excel.Chart chart = (Microsoft.Office.Interop.Excel.Chart)workbook.Charts.Add(missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Range chartRange = worksheet.get_Range("A1:A14", "B1:E14");//定义绘制图表范围 //来自codego.net 在指定范围绘制图表 chart.ChartWizard(chartRange, Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, missing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, 1, 1, true, "工程词典销量分析", "月份", "销量", missing); excel.DisplayAlerts = false;//设置保存Excel时不显示对话框 workbook.Save();//保存工作簿 workbook.Close(false, missing, missing);//关闭工作簿 CloseProcess("EXCEL");//关闭所有Excel进程 WBrowser_Excel.Navigate(tstxt_Excel.Text);//在窗体中重新显示Excel文件内容 }

[fzlrpsun]

现在遇到另一个问题，xls格式文件，用getsheetat 获取到的sheet 的tabcolorindex 是未创建的实例，导致不能给其复制，修改颜色，但是createsheet创建的sheet就没事