为excel创建边框 [英] Creating borders for excel

问题描述

我需要使用SSIS动态创建excel文件，我成功了。现在有两个限制。



1）我必须从表中获取查询，该表将是excel表的数据。

即我有一个表和我有一些报告ID为主键，每个报告都有不同的查询，并且必须根据报告ID调用相应的查询



表看起来像（t_myTable）：

 Report_ID Report_Name查询
  1  rep_1 选择 * 来自 table1 
  2  rep_2 选择 * 来自 table2 
  3  rep_3 选择 * 来自 table3 
  4  rep_4 选择 * 来自 table4 

我将在后端使用的查询是

 从 t_myTable 查询 code-keyword> where  Report_ID = 1 

此查询应该在该列中获取查询，并且应该执行该查询。我如何实现这个功能？



帮我逻辑。



2）excel I 已经创建的应该有边框，即数据所在的区域应该有边框。

考虑我的数据从单元格A1开始，它在单元格E50中结束应该有一个边界区分这个区域剩下的空单元格。

动态创建单元格数量未定义。



帮助我理解逻辑和语法为此





提前致谢:)

Sadiq

解决方案

Hello Sadiq，



我不太确定Point 1;但是你可以从下面提到的代码中得到一些关于第2点的提示：

尝试
 Dim excelApp作为新微软。 Office.Interop.Excel.Application 
 
 If（Not File.Exists（Path.Combine（AppDomain.CurrentDomain.BaseDirectory，TestApp.xlsx）））然后
 File.Create（Path .Combine（AppDomain.CurrentDomain.BaseDirectory，TestApp.xlsx））
 End if 
 
 Dim excelBook As Workbook = excelApp.Workbooks.Open（AppDomain.CurrentDomain.BaseDirectory&TestApp .xlsx）
 
使用excelBook 
 Dim sheet As Worksheet = excelBook.ActiveSheet 
 sheet.Range（A1，D4）。BorderAround（，XlBorderWeight.xlThick， XlColorIndex.xlColorIndexAutomatic）
 End 
 
 excelBook.Save（）
 Catch ex As Exception 
 MessageBox.Show（Exception occurred：& Environment.NewLine& amp; ; ex.Message）
结束尝试

请告诉我有关第1点的详细信息。

您可以尝试使用 Excel软件包。它非常简单使用



将你拉入数据表的所有数据都计入行数。

  Imports  OfficeOpenXml 
 
 使用 rng 作为 ExcelRange = ws.Cells（ 1 ， 1 ， 1 ，data.Columns.Count  -   1 ）'  ws.Cells（A1：K1） 
 rng.Style.Border.Left.Style = Style.ExcelBorderStyle.Thin 
 rng.Style .Border.Left.Color.SetColor（Color.Black）
 
 结束 使用 

I had a requirement of creating an excel file dynamically using SSIS and I succeeded in doing so. Now there are two constraints.

1) I have to fetch the query from a table which will be the data for the excel sheet.
i.e, I have a table and I have some reports with report id as primary key and every report has different queries and the respective query has to be called in with respect to the report id

Table looks like(t_myTable) :

Report_ID	Report_Name	Query
1	        rep_1	        select * from table1
2	        rep_2	        select * from table2
3	        rep_3	        select * from table3
4	        rep_4	        select * from table4

the query I''ll be using in the back-end is

Select Query from t_myTable where Report_ID=1

this query should fetch me the query in that column and it should be executed. How do I implement this functionality?

Help me with the logic.

2)The excel I''ve created should have borders i.e, the region where the data is present should have border.
Consider my data starts from cell A1 and it ends in the cell E50 there should be a border differentiating this region with the rest of the empty cells.
Number of cells are undefined as they are created dynamically.

Help me with the logic and syntax for this


Thanks in advance :)
Sadiq

解决方案

Hello Sadiq,

I am not quite sure of the Point 1; however you can get some hint for the point #2 from the code mentioned below:

Try
            Dim excelApp As New Microsoft.Office.Interop.Excel.Application

            If (Not File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestApp.xlsx"))) Then
                File.Create(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestApp.xlsx"))
            End If

            Dim excelBook As Workbook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory & "TestApp.xlsx")

            With excelBook
                Dim sheet As Worksheet = excelBook.ActiveSheet
                sheet.Range("A1", "D4").BorderAround(, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic)
            End With

            excelBook.Save()
        Catch ex As Exception
            MessageBox.Show("Exception occurred : " & Environment.NewLine & ex.Message)
        End Try

Please let me know with details of what you need about point #1.

you can try using Excel Package. its quite simple to use

put all the data that you pull into a datatable then count the number of rows.

Imports OfficeOpenXml

Using rng As ExcelRange = ws.Cells(1, 1, 1, data.Columns.Count - 1) 'ws.Cells("A1:K1")
rng.Style.Border.Left.Style = Style.ExcelBorderStyle.Thin
rng.Style.Border.Left.Color.SetColor(Color.Black)

 End Using

这篇关于为excel创建边框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！