2
How to make progress bar load as excel file import is in progress ?
My code so far:
ASPX:
<asp:FileUpload ID="FileUpload" runat="server" Style="margin-top: 4px" /><br />
<div id="progressbar" style="width:550px;">
<label id="label" text="0%"></label>
</div>
<asp:Label ID="label2" runat="server" Text=""></asp:Label>
<div id="result"></div>
Function:
function updateProgress() {
//var value = $("#progressbar").progressbar("option", "value");
if (value < 100) {
$("#progressbar").progressbar("value", value + 1);
document.getElementById("label").innerHTML = value + 1 + '%';
$("#<%=label2.ClientID%>").text("Processando...");
}
}
$("#<%=btnProcessar.ClientID%>").click(function () {
var intervalID = setInterval(updateProgress, 100);
$.ajax({
type: "POST",
url: "ConciliacaoPedidos.aspx/GetText",
contentType: "application/json; charset=utf-8",
dataType: "json",
async: true,
success: function(msg) {
$("#progressbar").progressbar("value", 100);
$("#<%=label2.ClientID%>").text("");
$("#result").text(msg.d);
clearInterval(intervalID);
}
});
});
Method . Cs:
public void ImportarArquivoExcel(string FilePath)
{
this.conexaoExcel(FilePath);
this.excelConn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = excelConn;
DataTable dtSheetnames = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataSet ds = new DataSet();
foreach (DataRow dr in dtSheetnames.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
StringBuilder query = new StringBuilder();
query.Append("AQUI EU MONTO A QUERY DO EXCEL")
cmd.CommandText = query.ToString();
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
oda.Fill(dt);
DataTable dtExcel = dt.Clone();
dtExcel.Columns[0].DataType = typeof(string);
dtExcel.Columns[1].DataType = typeof(double);
dtExcel.Columns[2].DataType = typeof(int);
dtExcel.Columns[3].DataType = typeof(double);
dtExcel.Columns[4].DataType = typeof(int);
dtExcel.Columns[5].DataType = typeof(decimal);
dtExcel.Columns[6].DataType = typeof(decimal);
foreach (DataRow row in dt.Rows)
{
dtExcel.ImportRow(row);
}
dt.Clear();
ds.Clear();
ds.Tables.Add(dtExcel);
}
excelConn.Close();
DataTable Exceldt = ds.Tables[0];
conexaoSQL();
SqlBulkCopy objbulk = new SqlBulkCopy(sqlConn);
try
{
//Mapeia as colunas de origem "Excel" para as colunas de destino "SQL"
objbulk.ColumnMappings.Add("PedConcEmpresa", "Campo1");
objbulk.ColumnMappings.Add("TEXTBOX36", "Campo2");
objbulk.ColumnMappings.Add("TEXTBOX41", "Campo3");
objbulk.ColumnMappings.Add("PRODUCTPK", "Campo4");
objbulk.ColumnMappings.Add("QUANTITY", "Campo5");
objbulk.ColumnMappings.Add("PRODUCTPRICE", "Campo6");
objbulk.ColumnMappings.Add("TEXTBOX27", "Campo7");
//Seta a tabela para destino
objbulk.DestinationTableName = "dbo.Tabela";
//Faz a inserção na tabela de destino
sqlConn.Open();
objbulk.WriteToServer(Exceldt);
sqlConn.Close();
}
catch (Exception ex)
{
metodos.AnalizarErro(ex.Message, Request.CurrentExecutionFilePath, this.Page, this.GetType());
mensagens.ExibirMensagem("Erro", mensagens.strMsgErroGenerico, false, this.Page, this.GetType());
}
finally
{
// CLEAR.
objbulk.Close();
objbulk = null;
}
}