IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
  • 登录 注册

    [转]-------------------------数字转换成EXCEL列名---------------------------------

    fredrickhu发表于 2014-01-07 17:41:27
    love 0
    alter function col_rev(
     @i_version int, -- Excel 版本号 
     @i_col int          -- 字段号(数值表示)
     )
    returns varchar(20)
    as
    begin
      declare @v_devide int;
      declare @v_mod int;
      declare @v_col int;
      declare @str nvarchar(20);
      set @v_col=@i_col;
      set @str = '';
      
      if @i_version=2003 and @v_col<=256  -- Excle 2003 最多256列
      begin
        if @v_col>26
        begin
          set @str= (CASE when (@v_col%26)=0 then char(ascii('A')+(@v_col/26)-2)+'Z'
                     ELSE char(ascii('A')+(@v_col/26)-1)+char(ascii('A')+(@v_col%26)-1) end); 
        end
        else if @v_col=26
        begin
          set @str = 'Z';
        end
        else
        begin
          select @str=char(ascii('A')+(@v_col%26)-1);
        end
      end
      return @str;
    end
    
    select '76' AS C1, dbo.col_rev(2003,76) as col UNION ALL
    select '77' AS C1, dbo.col_rev(2003,77) as col UNION ALL
    select '78' AS C1, dbo.col_rev(2003,78) as col UNION ALL
    select '79' AS C1, dbo.col_rev(2003,79);
    


    比较古老的方法了,在网上看见了,COPY过来备注一下。



沪ICP备19023445号-2号
友情链接