SQL Server 2005 OPENROWSET插入Excel - 文本到数字格式 [英] SQL Server 2005 OPENROWSET insert into Excel - text to number formatting

问题描述

所以这不是一个大问题，但是Google没有回复任何近期（大多数是2007年左右）关于它可能是一个Excel驱动程序问题...我来这里问这个问题，我发现作为一个答案

我的问题：

编写一个动态的OPENROWSET查询清除并插入新的数据发送给特定用户的报告 - 基本Excel报告包含一些公式，其中计算基于插入的数据的关闭和总和乘法公式似乎工作正常，但除非总计在Excel中正确格式为数字而不是文本的总和将无法正常工作...

解决方案必须是基于自由格式（如果有解决方案），我不想将所有我的代码转移到SSIS ...

更新/插入代码：

  SELECT @sqlu ='UPDATE OPENROWSET（'+ $ b $''''Microsoft.ACE.OLEDB.12.0''，'+ 
'''Ex cel 12.0; Database ='+ @File +'; HDR = YES''，'+ 
'''SELECT * FROM [Base $ B1：G601]''）
 SET 
 col1 =''''，col2 =''''，col3 =''''，col4 =''''，col5 =''''，col6 ='''''
 
 
 EXEC（@sqlu）
 
 
 SELECT @sqli ='INSERT INTO OPENROWSET（'+ $ b $'''Microsoft.ACE.OLEDB.12.0''，'+ 
'''Excel 12.0; Database ='+ @File +'; HDR = NO''，'+ 
'''SELECT * FROM [Base $ B1：G2]''）
 
 SELECT col1，col2，col3，col4，col5，col6 
 FROM [DATABASE]。[dbo] .tbl_CIM 
 WHERE col1 IN（'''+ @area +'''） '
 
 
 EXEC（@sqli）
  

第二个问题现在可能不是问题，但是后来 - 在插入之后，似乎在结果周围创建了边界。在结果转移过程中，是否还有一种可能的方法来删除这些结果？

任何帮助或资源来研究是否是驱动程序问题，我都赞不绝口。 / p>

解决方案

我知道的唯一方法是在您写入的Excel工作表中创建一个虚拟的数据行。
将伪造的数据放在这一行中，格式与您希望导出数据的格式相同。如果需要，可以隐藏这一行。

这应该解决问题，但是你必须处理虚拟行，如果你使用的是数据透视表的数据，这可能会令人讨厌

So it's not a huge deal, but with Google not returning anything more recent (mostly 2007 or so) about it possibly being an Excel driver issue...I came here to ask the question as what I found as an "answer" was not acceptable without further research.

My problem:

Writing a dynamic OPENROWSET query clear and insert new data to send off a report to specific users - the base excel report contains some formulas in which it calculates off and sums based off the inserted data and the multiplication formulas seem to work fine, but unless the totals are formatted properly in Excel as "numbers" instead of text the sum will not function...

Solution must be free-form based (if there is a solution at all), I don't want to transfer all my code to SSIS...

Update/Insert code:

    SELECT @sqlu = 'UPDATE OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File + ';HDR=YES'', ' +
    '''SELECT * FROM [Base$B1:G601]'')
    SET 
    col1 = '''', col2 = '''', col3 = '''', col4 = '''', col5 = '''', col6 = '''' '


    EXEC (@sqlu)


    SELECT @sqli = 'INSERT INTO OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File + ';HDR=NO'', ' +
    '''SELECT * FROM [Base$B1:G2]'')

    SELECT col1, col2, col3, col4, col5, col6
    FROM [DATABASE].[dbo].tbl_CIM
    WHERE col1 IN(''' + @area + ''')'


    EXEC (@sqli)

The second problem and it may not be a problem for now but later - after the insert, it seems to create borders around the results. Is there also a potential way to remove those during the result transfer?

Any help or sources to research if it is a driver issue is appreciated as I could find none.

解决方案

The only way I know of is to create a dummy row of data in the Excel sheet you are writing to. Put fake data in this row in the same format that you want the data to export in. You can hide this row if required.

This should solve both problems, however you have to deal with the dummy row, which can be annoying if you are using the data for a pivot table

这篇关于SQL Server 2005 OPENROWSET插入Excel - 文本到数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！