L'esempio (senza UC) si appoggia ad una cartella che si chiama "Upload_Files", contenuta in un'altra che si chiama
"XLSB_Read_Bulk_Export" che sta nella root della solution.
aspx:
codice:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="IncredibileBulk.aspx.cs" Inherits="IncredibileBulk" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>XLSB / Bulk</title>
</head>
<body style="background-color: #fffff0">
<form id="form1" runat="server">
<div>
<div style="font-style: italic; color: black;">
<h1>1. Upload // 2. Lettura XLS // 3. SqlBulkCopy in tabella</h1>
</div>
<hr />
<h2>Seleziona il file XLS sorgente:</h2>
<asp:FileUpload ID="fupXLS" runat="server" ToolTip="Carica File XLS" />
<asp:Button ID="btUpload" runat="server" Text="Upload al server" OnClick="btUpload_Click" />
<asp:Label ID="lb" runat="server" Font-Size="Large" Font-Bold="true"></asp:Label>
<br />
<br />
<br />
<hr />
<div align="center">
<asp:Button BorderStyle="Solid" Width="130px" Height="50px" ID="btReadXLSB" runat="server" Text="Leggi File XLS" OnClick="btReadXLSB_Click" />
<br />
<asp:Label ID="lbSelectXSLB" runat="server" Font-Size="Medium" Font-Bold="true"></asp:Label>
<br />
<br />
<asp:GridView GridLines="Both" Width="50%" ID="gv" runat="server"
BackColor="White" BorderColor="#000" BorderStyle="None" BorderWidth="1px" CellPadding="3">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
<AlternatingRowStyle BackColor="LightGray" />
</asp:GridView>
<br />
<asp:Button ID="btDownloadXLS" BorderStyle="Solid" Width="130px" Height="50px" runat="server" Text="Scarica XLS" OnClick="btDownloadXLS_Click" />
<asp:Button ID="btBulk" runat="server" Text="BulkCopy !" OnClick="btBulk_Click" BorderStyle="Solid" Width="130px" Height="50px" /><br />
<br />
<br />
</div>
</div>
</form>
</body>
</html>
C#
codice:
using System;
using System.Data;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using ToolsLibrary.nsDatabaseLibrary;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class IncredibileBulk : System.Web.UI.Page
{
private OleDbConnection internal_SQLCONN;
private OleDbDataAdapter internal_SQLADAPTER;
private DataSet internal_DATASET;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
btUpload.Enabled = true; // per upload del file
fupXLS.Enabled = true; // per upload del file
btDownloadXLS.Visible = false; // per scaricare il file
btBulk.Visible = false;
btReadXLSB.Enabled = false; // per leggere il file
lb.Text = String.Empty;
lbSelectXSLB.Text = String.Empty;
string js = "if(!confirm('Questa operazione copierà il foglio XLS sulla tabella SQLServer. Continuare ?')) return false;";
btBulk.Attributes.Add("OnClick", js);
}
}
protected void btUpload_Click(object sender, EventArgs e)
{
// Percorso applicazione RestInPeace
string percorsoApplicazione = AppDomain.CurrentDomain.BaseDirectory.ToString();
// La cartella deve avere i permessi di scrittura per "Everyone"
String pathSalvataggio = percorsoApplicazione + "XLSB_Read_Bulk_Export\\RepositoryUpload";
if (fupXLS.HasFile)
{
String nomeFile = fupXLS.FileName;
pathSalvataggio += "\\" + nomeFile; // concatena il nome file
Session["FileName"] = nomeFile;
bool tipoFileOK = true; // todo:: controllare con il mime
if (tipoFileOK == true) // dovra' essere a true
{
try
{
fupXLS.SaveAs(pathSalvataggio);
lb.Text = " UPLOAD OK !";
btReadXLSB.Enabled = true;
btUpload.Enabled = false;
fupXLS.Enabled = false;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
// load del foglio xls
protected void btReadXLSB_Click(object sender, EventArgs e)
{
// Percorso applicazione
string percorsoApplicazione = AppDomain.CurrentDomain.BaseDirectory.ToString();
// La cartella deve avere i permessi di scrittura per "Everyone"
String pathSalvataggio = percorsoApplicazione + "XLSB_Read_Bulk_Export\\RepositoryUpload\\" + Session["FileName"].ToString();
Session.Remove("FileName");
try
{
// Connessione
try
{
internal_SQLCONN = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ pathSalvataggio + ";Mode=Share Deny None;Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False;IMEX=1'");
internal_SQLCONN.Open();
}
catch (OleDbException ex)
{
internal_SQLCONN.Close();
throw ex;
}
catch (Exception ex)
{
internal_SQLCONN.Close();
throw ex;
}
internal_DATASET = new DataSet();
internal_SQLADAPTER = new OleDbDataAdapter("SELECT * FROM [foglio1$]", internal_SQLCONN); // Select dal <Foglio1> del file Excel
try
{
internal_SQLADAPTER.Fill(internal_DATASET);
btReadXLSB.Enabled = false;
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
internal_SQLCONN.Close();
}
gv.DataSource = internal_DATASET.Tables[0];
gv.DataBind();
// Aggiunta in sessione del set di dati
Session.Add("dt_BulkSource", internal_DATASET.Tables[0]);
//btDownloadXLS.Visible = true;
btBulk.Visible = true;
}
catch (Exception exx)
{
throw exx;
}
}
// INCREDIBILE BULK
protected void btBulk_Click(object sender, EventArgs e)
{
DataTable dt1 = (DataTable)Session["dt_BulkSource"];
Session.Remove("dt_BulkSource");
//CARICAMENTO su SQL Server
string connstring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["STUDIO2O15ConnectionString"].ConnectionString;
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connstring))
{
bulkCopy.DestinationTableName = "dbo.[TB_XLSB]";
try
{
// Da sorgente XLS a destinazione SQLServer
bulkCopy.WriteToServer(dt1);
// Reset ============================
gv.DataSource = null;
gv.DataBind();
btDownloadXLS.Visible = false;
btBulk.Visible = false;
lb.Text = String.Empty;
// ==================================
ToolsLibrary.nsScriptLibrary.ScriptLibrary.Alert(this, "XLS copiato su SQL Server.");
// reset
btUpload.Enabled = true; // per upload del file XLSB
fupXLS.Enabled = true; // per upload del file XLSB
btBulk.Visible = false;
btReadXLSB.Enabled = false; // per leggere il file XLSB
lb.Text = String.Empty;
lbSelectXSLB.Text = String.Empty;
}
catch (Exception ex)
{
ToolsLibrary.nsScriptLibrary.ScriptLibrary.Alert(this, ex.Message);
}
finally
{
bulkCopy.Close();
}
}
}
// verifica che il gridview sia renderizzato
public override void VerifyRenderingInServerForm(Control control)
{
//
}
// Download della grid
protected void btDownloadXLS_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.AppendHeader("content-disposition", "attachment;filename=EXPORT.xls");
Response.ContentType = "application/excel";
StringWriter stringwriter = new StringWriter();
HtmlTextWriter htmtextwriter = new HtmlTextWriter(stringwriter);
gv.HeaderRow.Style.Add("background-color", "#006699");
foreach (GridViewRow gridviewrow in gv.Rows)
{
gridviewrow.BackColor = System.Drawing.Color.White;
foreach (TableCell gridviewrowtablecell in gridviewrow.Cells)
{
gridviewrowtablecell.Style["background-color"] = "#ffffff";
}
}
gv.RenderControl(htmtextwriter);
Response.Write(stringwriter.ToString());
Response.End();
}
}
Sono 3 step:
- upload
- leggi xls
- esporta (se vuoi) di nuovo l'xls
- dai la cera
- togli la cera
- fai la bulk nel db
Rispondi quotando
non so. Magari provo dopo a casa.
