notlevire

terça-feira, 15 de dezembro de 2015

C# funções para abertura de excel

#region funções para abertura de excel

public static DataTable XlsxToDataSet(String pFile, String pNomeAba)
{
return XlsxToDataSet(pFile, pNomeAba, VerVersaoExcel(), false);
}

public static DataTable XlsxToDataSet(String pFile, String pNomeAba, Boolean pNaoUtilizarImex)
{
return XlsxToDataSet(pFile, pNomeAba, VerVersaoExcel(), pNaoUtilizarImex);
}

public static DataTable XlsxToDataSet(String pFile, String pNomeAba, Int32 pVersao)
{
return XlsxToDataSet(pFile, pNomeAba, pVersao, false);
}

public static DataTable XlsxToDataSet(String pFile, String pNomeAba, Int32 pVersao, Boolean pNaoUtilizarImex)
{
return XlsxToDataSet_2(pFile, pNomeAba, pVersao, pNaoUtilizarImex, false);
}

//http://www.connectionstrings.com/excel-2003/
/// <summary>
///
/// </summary>
/// <param name="pFile"></param>
/// <param name="pNomeAba"></param>
/// <param name="pVersao"></param>
/// <param name="pImex">"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.</param>
/// <returns></returns>
public static DataTable XlsxToDataSet_2(String pFile, String pNomeAba, Int32 pVersao, Boolean pNaoUtilizarImex, Boolean pSegundaTentativa)
{
String extension = string.Empty;
try
{
DataSet ds = new DataSet();
extension = Path.GetExtension(pFile);
String iImex = (pNaoUtilizarImex ? "" : "IMEX=1;");

Boolean iVersaoNova = (pVersao >= 12 && extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase));
if (pSegundaTentativa)
iVersaoNova = !iVersaoNova;

OleDbDataAdapter MyCommand;
//HDR=YES;IMEX=1; //Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text // SÓ FUNCIONA COM 'HDR=NO;IMEX=1', MAS 'HDR=NO' FICA SEM O CABEÇALHO NA PRIMEIRA LINHA
OleDbConnection MyConnection;
if (iVersaoNova) //12.0 -> Excel 2007
MyConnection = new OleDbConnection(String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;{1}""", pFile, iImex));
//MyConnection = new OleDbConnection(String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;""", pFile));
else
MyConnection = new OleDbConnection(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;{1}""", pFile, iImex));

MyCommand = new System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", pNomeAba), MyConnection);
MyCommand.TableMappings.Add("Table", "Table");
MyCommand.Fill(ds);
MyConnection.Close();

return ds.Tables[0];
}
catch (Exception ex)
{
if (pSegundaTentativa || pVersao < 12)
{
if (_ExPrimeiraTentativa == null)
_ExPrimeiraTentativa = ex;
throw new Exception("Error: " + _ExPrimeiraTentativa.Message.ToString() + Environment.NewLine + Environment.NewLine
+ "pFile: " + pFile + Environment.NewLine + "pNomeAba: " + pNomeAba + " - pVersao: " + pVersao.ToString() + " - extension: " + extension, ex);
}
else
{
_ExPrimeiraTentativa = ex;
String iEmail = "seuemail@provedor.com.br";
if (ConfigurationManager.AppSettings["email_Erro"] != null)
iEmail = ConfigurationManager.AppSettings["email_Erro"].Trim();
Email.Enviar("", iEmail, "Erro 1 - Máq: " + WindowsIdentity.GetCurrent().Name + " - Abrir Excel", MontarMsgErro(ex));
return XlsxToDataSet_2(pFile, pNomeAba, pVersao, pNaoUtilizarImex, true);
}
}
}
static Exception _ExPrimeiraTentativa;

public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)
{
string header = "No";
string sql = string.Empty;
DataTable dataTable = null;
string pathOnly = string.Empty;
string fileName = string.Empty;
//try
//{
pathOnly = Path.GetDirectoryName(path);
fileName = Path.GetFileName(path);
sql = @"SELECT * FROM [" + fileName + "]";
if (IsFirstRowHeader)
{
header = "Yes";
}
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
";Extended Properties=\"Text;HDR=" + header + "\""))
{
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable = new DataTable();
//dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dataTable);
}
}
connection.Close();
}
//}
//finally
//{
//}
return dataTable;
}

public static String[] GetExcelSheetNames(string excelFile)
{
return GetExcelSheetNames(excelFile, VerVersaoExcel());
}

/// <summary>
/// This mehtod retrieves the excel sheet names from
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
public static String[] GetExcelSheetNames(string excelFile, Int32 pVersao)
{
//Stopwatch stopwatch = new Stopwatch();
//stopwatch.Start();

OleDbConnection objConn = null;
DataTable dt = null;

try
{
// Connection String. Change the excel file to the file you will search.
String connString;
if (pVersao >= 12) //12.0 -> Excel 2007
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties=Excel 12.0;";
else
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{

if (!String.IsNullOrEmpty(row["TABLE_NAME"].ToString()) && (row["TABLE_NAME"].ToString().EndsWith("$") ||
row["TABLE_NAME"].ToString().EndsWith("$'")))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$", "").Replace("'", "");
i++;
}
}
excelSheets = excelSheets.Where(x => !string.IsNullOrEmpty(x)).ToArray();

//// Loop through all of the sheets if you want too...
//for (int j = 0; j < excelSheets.Length; j++)
//{
// // Query each excel sheet.
//}

return excelSheets;
}
catch (Exception)
{
throw;
//return null;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
objConn = null;
}
if (dt != null)
{
dt.Dispose();
dt = null;
}
//stopwatch.Stop();
//MessageBox.Show(stopwatch.Elapsed.ToString());
}
}

public static Int32 VerVersaoExcel()
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
String iVersao = excel.Version;

// Release the Application object
excel.Quit();
excel = null;

// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();

//System.Globalization.CultureInfo cultura = new System.Globalization.CultureInfo("pt-BR");
//System.Threading.Thread.CurrentThread.CurrentCulture = cultura;

Decimal intVersao = 0;
Decimal.TryParse(iVersao.Replace(".", ","), out intVersao);
return Convert.ToInt32(intVersao);
}

/// <summary>
/// This mehtod retrieves the excel sheet names from
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
public static String[] GetExcelSheetNamesOld(string excelFile)
{
//Stopwatch stopwatch = new Stopwatch();
//stopwatch.Start();

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(excelFile);

String[] excelSts = new String[wb.Sheets.Count];

int j = 0;
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
{
excelSts[j] = (sheet.Name);
j++;
}

// Close the Workbook
wb.Close();
wb = null;

// Release the Application object
excel.Quit();
excel = null;

// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();

//stopwatch.Stop();
//MessageBox.Show(stopwatch.Elapsed.ToString());

return excelSts;
}

#endregion

C# Montar Mensagem de Erro em HTML

/// <summary>Rotina que retorna um objeto string contendo uma tabela html com os dados referente ao erro enviado por parâmetro
/// </summary>
/// <param name="pObjErr">Objeto Exception com os dados do erro</param>
/// <returns>Retorna um objeto string contendo uma tabela html com os dados referente ao erro</returns>
/// <example>Escrevendo diretamente um arquivo:
/// <code>
/// Geral.EscreverArquivo(token, Geral.MontarMsgErro(e.Error));
/// </code>
/// Armazenando em uma variável para posterior uso:
/// <code>
/// iBody = Geral.MontarMsgErro(objErr);
/// </code>
/// </example>
public static string MontarMsgErro(Exception pObjErr)
{
string pErrorMsg = pObjErr.Message.ToString();
string pInner = (pObjErr.InnerException == null ? String.Empty : pObjErr.InnerException.ToString());
string pType = (pObjErr.GetType().FullName == null ? String.Empty : pObjErr.GetType().FullName.ToString());
string pStack = (pObjErr.StackTrace == null ? String.Empty : pObjErr.StackTrace.ToString());
string pTargetSite = (pObjErr.TargetSite == null ? String.Empty : pObjErr.TargetSite.ToString());
string pSource = (pObjErr.Source == null ? String.Empty : pObjErr.Source.ToString());

return @"
<html content=""text/html; charset=UTF-8"">
<body bgcolor=""#FFFFFF"">
<table width=""85%"" border=""1"" cellspacing=""0"" cellpadding=""1"" bordercolor=""#999999"">
<tr bgcolor=""#CCCCFC"">
<td width=""100%"" colspan=""2"" height=""20"">
<div align=""center"">Dados do Erro</div>
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Date
</td>
<td width=""85%"">
 " + DateTime.Now.ToString(_FormatoDataHora) + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Error Message
</td>
<td width=""85%"">
" + pErrorMsg + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Inner Exception
</td>
<td width=""85%"">
" + pInner + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Exception Type
</td>
<td width=""85%"">
" + pType + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Stack Trace
</td>
<td width=""85%"">
" + pStack + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Target Site
</td>
<td width=""85%"">
" + pTargetSite + @" 
</td>
</tr>
<tr bgcolor=""#CCFFCC"">
<td width=""15%"">
Source
</td>
<td width=""85%"">
" + pSource + @" 
</td>
</tr>
</table>
</body>
</html>";
}

Datatable - Transformar colunas em linhas

public static DataTable Transformar_Colunas_Em_Linhas(DataTable iDt)
{
//gera o erro no datagridview "A soma dos valores FillWeight das colunas não pode ultrapassar 65535." - "Sum of the columns' FillWeight values cannot exceed 65535"
if (iDt.Rows.Count > 0)
{
DataTable iDtId = new DataTable();
iDtId.Columns.Add("Campo");
iDtId.Columns.Add("Valor");

for (int i = 1; i < iDt.Rows.Count; i++)
iDtId.Columns.Add("Valor_" + (i+1).ToString());

foreach (DataColumn iDc in iDt.Columns)
{
DataRow iDr = iDtId.NewRow();
iDr["Campo"] = iDc.ColumnName.ToString();
iDr["Valor"] = iDt.Rows[0][iDc.ColumnName].ToString();
for (int i = 1; i < iDt.Rows.Count; i++)
{
iDr["Valor_" + (i + 1).ToString()] = iDt.Rows[i][iDc.ColumnName].ToString();
}
iDtId.Rows.Add(iDr);
}
return iDtId;
}
return null;
}

sexta-feira, 26 de outubro de 2012

transformando várias linhas em uma só coluna

utilizando cursor e tabela virtual

concatenação de tabelas

neste exemplo vamos consultar uma tabela NxN Produto e Fornecedor, vamos listar todos os produtos e para cada produto vamos listar os seus fornecedores cadastrados, mas somente em uma única linha por produto separados por vírgula

neste exemplo também usaremos alguns recursos interessantes como tabela virtual (tabela temporária) e cursor

set nocount on
--tabelas para testes
create table produto (ID_PRODUTO int, NOM_PRODUTO varchar(50))
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (1, 'PRODUTO 1')
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (2, 'PRODUTO 2')
insert into produto (ID_PRODUTO, NOM_PRODUTO ) values (3, 'PRODUTO 3')
        
create table fornecedor(ID_FORNECEDOR int, NOM_FORNECEDOR varchar(50))
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (1, 'FORNECEDOR 1')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (2, 'FORNECEDOR 2')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (3, 'FORNECEDOR 3')
insert into fornecedor (ID_FORNECEDOR, NOM_FORNECEDOR ) values (4, 'FORNECEDOR 4')
               
create table forn_prod (ID_PRODUTO int, ID_FORNECEDOR int)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 1)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (2, 1)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 2)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (2, 2)
insert into forn_prod (ID_PRODUTO, ID_FORNECEDOR ) values (1, 4)
        
        
        
--tabela temporaria
create table #temp (NOM_PRODUTO varchar(50), NOM_FORNECEDOR varchar(4000))
        
--select distinct para buscar somente produtos que estejam na tabela forn_prod
--cursor x: produtos
declare x cursor for select distinct forn_prod.ID_PRODUTO, NOM_PRODUTO from forn_prod
                                inner join produto on produto.ID_PRODUTO = forn_prod.ID_PRODUTO
        --variaveis para o cursor x
        declare @ID_PRODUTO int
        declare @NOM_PRODUTO varchar(50)
        --variável para concatenar o nome dos fornecedores
        declare @NOM_FORNECEDOR_conc varchar(8000)
        open x
        fetch next from x into @ID_PRODUTO,@NOM_PRODUTO
        while @@fetch_Status=0
        begin
                --zerando variável de concatenação
                set @NOM_FORNECEDOR_conc = ''
                --cursor y: fornecedores relacionados com os produtos, vai concatenar os fornecedores na variável @NOM_FORNECEDOR_conc
                declare y cursor for select NOM_FORNECEDOR from fornecedor
                                        inner join forn_prod on forn_prod.ID_FORNECEDOR = fornecedor.ID_FORNECEDOR
                                        where forn_prod.ID_PRODUTO = @ID_PRODUTO
                --variavel para o cursor y
                declare @NOM_FORNECEDOR varchar(50)
                open y
                fetch next from y into @NOM_FORNECEDOR
                while @@fetch_Status=0
                begin
                        --concatenando fornecedores na variável @NOM_FORNECEDOR_conc
                        set @NOM_FORNECEDOR_conc = @NOM_FORNECEDOR_conc + @NOM_FORNECEDOR + ', '
        
                        --loop do cursor y
                        fetch next from y into @NOM_FORNECEDOR
                end
                --fim do cursor y
                close y
                deallocate y  
        
                --retira última virgula
                set @NOM_FORNECEDOR_conc = substring(@NOM_FORNECEDOR_conc, 1, len(@NOM_FORNECEDOR_conc)-1)
                --insere na tabela virtual
                insert into #temp (NOM_PRODUTO, NOM_FORNECEDOR )
                values (@NOM_PRODUTO, @NOM_FORNECEDOR_conc)
        
                --loop do cursor x
                fetch next from x into @ID_PRODUTO,@NOM_PRODUTO
        end
--fim do cursor x
close x
deallocate x
        
--consulta da tabela temporaria
select * from #temp
       
--apagando tabela temporaria
drop table #temp
--apagando tabelas para testes
drop table produto
drop table fornecedor
drop table forn_prod

Resultado:

NOM_PRODUTO                    NOM_FORNECEDOR

PRODUTO 1                      FORNECEDOR 1, FORNECEDOR 2, FORNECEDOR 4
PRODUTO 2                      FORNECEDOR 1, FORNECEDOR 2

AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.

Gerando Somatórios

alguns tipos de totalização de dados

totalizando, somando dados:

no SQL Server, para estas operações de somar e totalizar podemos usar as seguintes funções:
- COMPUTE: realiza somatórios, nos agrupamentos divide os grupos em result-sets diferentes.
Podem ser usados outros operadores no compute: { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }

- ROLLUP: realiza somatórios, nos agrupamentos mantém em um único result-sets

- CUBE: realiza somatórios, nos agrupamentos mantém em um único result-sets e no final gera totais por grupos

Dados para testes:

set nocount on
declare @tab table (IDProduto char(5), Nome varchar(10), valor money)
insert into @tab (IDProduto, Nome, valor) values (1, 'Norte', 10)
insert into @tab (IDProduto, Nome, valor) values (1, 'Sul', 20)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 5)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 15)
insert into @tab (IDProduto, Nome, valor) values (2, 'Norte', 25)
insert into @tab (IDProduto, Nome, valor) values (1, 'Leste', 10)
insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 15)
insert into @tab (IDProduto, Nome, valor) values (3, 'Oeste', 5)
insert into @tab (IDProduto, Nome, valor) values (3, 'Norte', 5)

Exemplo com COMPUTE:

select IDProduto, Nome, valor from @tab
                  order by IDProduto
                  compute sum(valor) by IDProduto
                  compute sum(valor)

resultado da query acima:

                  IDProduto Nome       valor                             
                  --------- ---------- --------------------- 
                  1              Norte    10.0000
                  1              Sul      20.0000
                  1              Leste    10.0000
                  
                                                           sum
                                                           =====================
                                                           40.0000
                  
                  
                  IDProduto Nome           valor                                 
                  --------- ---------- --------------------- 
                  2              Norte    25.0000
                  2              Norte    5.0000
                  2              Norte    15.0000
                  
                                                           sum
                                                           =====================
                                                           45.0000
                  
                  
                  IDProduto Nome           valor                                 
                  --------- ---------- --------------------- 
                  3              Oeste    15.0000
                  3              Oeste    5.0000
                  3              Norte    5.0000
                  
                                                           sum
                                                           =====================
                                                           25.0000
                  
                  
                                                           sum
                                                           =====================
                                                           110.0000

Exemplo com ROLLUP:

select 
                         case when (grouping(IDProduto)=1) then 'TOTAL'
                         else isnull(IDProduto, 'desconhecido') end as IDProduto, 
                         case when (grouping(Nome)=1) then 'SUB-TOTAL'
                         else isnull(Nome, 'desconhecido') end as Nome,
                         sum(valor) as valor 
                 from @tab
                 group by IDProduto, Nome 
                 WITH ROLLUP

resultado da query acima:

                IDProduto Nome       valor                               
                --------- ---------- --------------------- 
                1                Leste    10.0000
                1                Norte    10.0000
                1                Sul      20.0000
                1                SUB-TOTAL  40.0000
                2                Norte    45.0000
                2                SUB-TOTAL  45.0000
                3                Norte    5.0000
                3                Oeste    20.0000
                3                SUB-TOTAL  25.0000
                TOTAL    SUB-TOTAL  110.0000

Exemplo com CUBE:

select 
                        case when (grouping(IDProduto)=1) then 'TOTAL'
                        else isnull(IDProduto, 'desconhecido') end as IDProduto, 
                        case when (grouping(Nome)=1) then 'SUB-TOTAL'
                        else isnull(Nome, 'desconhecido') end as Nome,
                        sum(valor) as valor 
                from @tab
                group by IDProduto, Nome 
                WITH CUBE

resultado da query acima:

                IDProduto Nome       valor                               
                --------- ---------- --------------------- 
                1                Leste    10.0000
                1                Norte    10.0000
                1                Sul      20.0000
                1                SUB-TOTAL  40.0000
                2                Norte    45.0000
                2                SUB-TOTAL  45.0000
                3                Norte    5.0000
                3                Oeste    20.0000
                3                SUB-TOTAL  25.0000
                TOTAL    SUB-TOTAL  110.0000
                TOTAL    Leste    10.0000
                TOTAL    Norte    60.0000
                TOTAL    Oeste    20.0000
                TOTAL    Sul            20.0000

no exemplo podemos observar tb. o uso da função GROUPING, com esta função verificamos se o retorno refere-se a uma linha de sub-totais ou a uma linha de totais

AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.