/// <summary>
/// Handles the Load event of the Page control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
Title = "Импорт - LeadForce";
if (Page.RouteData.Values["id"] != null)
{
_importId = Guid.Parse(Page.RouteData.Values["id"] as string);
}
hlCancel.NavigateUrl = UrlsData.AP_Imports();
if (!Page.IsPostBack)
{
ViewState["SheetName"] = string.Empty;
BindData();
BindImportColumns();
_importColumnRules = DataManager.ImportColumnRules.SelectByImportID(_importId);
BindImportColumnRules();
}
else
{
if (Request.Form["__EVENTTARGET"] == "ctl00$LabitecPage$ctl02$ContentHolder$lbtnSave") // !!!
{
isPreviewed = true;
}
}
RadAjaxManager.GetCurrent(Page).AjaxSettings.AddAjaxSetting(pnlWarning, pnlWarning);
}
/// <summary>
/// Handles the OnClick event of the lbtnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void lbtnSave_OnClick(object sender, EventArgs e)
{
Save();
Response.Redirect(UrlsData.AP_Imports());
}
/// <summary>
/// Handles the OnClick event of the lbtnSaveAndImport control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void lbtnSaveAndImport_OnClick(object sender, EventArgs e)
{
Server.ScriptTimeout = 1200;
Save();
if (uploadedFile.UploadedFiles.Count > 0)
{
if (ddlType.SelectedValue.ToEnum <ImportType>() == ImportType.Excel) // Excel
{
var sheet = ViewState["SheetName"].ToString();
IExcelDataReader excelReader = null;
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (excelReader != null)
{
var result = excelReader.AsDataSet();
ddlSheet.Items.Clear();
foreach (DataTable table in result.Tables)
{
ddlSheet.Items.Add(new ListItem(table.TableName, table.TableName));
}
if (!string.IsNullOrEmpty(sheet) && ddlSheet.Items.FindByValue(sheet) != null)
{
ddlSheet.Items.FindByValue(sheet).Selected = true;
}
else
{
sheet = ddlSheet.Items[0].Value;
ViewState["SheetName"] = sheet;
}
if (result.Tables[sheet].Rows.Count > 0)
{
var tmpImportTableName = string.Format("TmpImport_{0}", _importId.ToString().Replace("-", ""));
var sqlTableCreator = new SqlTableCreator();
sqlTableCreator.Connection = new SqlConnection(ConfigurationManager.AppSettings["ADONETConnectionString"]);
sqlTableCreator.Connection.Open();
sqlTableCreator.DestinationTableName = tmpImportTableName;
sqlTableCreator.CreateFromDataTable(result.Tables[sheet]);
sqlTableCreator.Connection.Close();
if (txtFirstRow.Value > 1) // удаление лишних строк
{
for (int i = 1; i < txtFirstColumn.Value; i++)
{
result.Tables[sheet].Rows.RemoveAt(i - 1);
}
}
if (cbIsFirstRowAsColumnNames.Checked)
{
result.Tables[sheet].Rows.RemoveAt(0);
}
BulkInsertDataTable(ConfigurationManager.AppSettings["ADONETConnectionString"], tmpImportTableName, result.Tables[sheet]);
DataManager.Import.DoImport(_importId);
}
}
}
else // CSV
{
var sr = new StreamReader(uploadedFile.UploadedFiles[0].InputStream, Encoding.GetEncoding("windows-1251"));
var csvDataTable = new DataTable();
string strTextFromFile = "";
string[] strArrSplitText = null;
while ((strTextFromFile = sr.ReadLine()) != null)
{
var options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline | RegexOptions.IgnoreCase));
var r = new Regex(string.Format(@"\{0}(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))", CsvSeparator), options);
strArrSplitText = r.Split(strTextFromFile);
for (int i = 0; i < strArrSplitText.Length; i++)
{
strArrSplitText[i] = strArrSplitText[i].Trim('\"');
}
if (csvDataTable.Columns.Count == 0)
{
for (int i = 0; i < strArrSplitText.Length; i++)
{
csvDataTable.Columns.Add("Column" + (i + 1).ToString(), typeof(string));
}
}
var row = csvDataTable.NewRow();
for (int i = 0; i < strArrSplitText.Length; i++)
{
row[i] = strArrSplitText[i];
}
csvDataTable.Rows.Add(row);
}
var tmpImportTableName = string.Format("TmpImport_{0}", _importId.ToString().Replace("-", ""));
var sqlTableCreator = new SqlTableCreator();
sqlTableCreator.Connection = new SqlConnection(ConfigurationManager.AppSettings["ADONETConnectionString"]);
sqlTableCreator.Connection.Open();
sqlTableCreator.DestinationTableName = tmpImportTableName;
sqlTableCreator.CreateFromDataTable(csvDataTable);
sqlTableCreator.Connection.Close();
if (txtFirstRow.Value > 1) // удаление лишних строк
{
for (int i = 1; i < txtFirstColumn.Value; i++)
{
csvDataTable.Rows.RemoveAt(i - 1);
}
}
if (cbIsFirstRowAsColumnNames.Checked)
{
csvDataTable.Rows.RemoveAt(0);
}
BulkInsertDataTable(ConfigurationManager.AppSettings["ADONETConnectionString"], tmpImportTableName, csvDataTable);
DataManager.Import.DoImport(_importId);
}
}
Response.Redirect(UrlsData.AP_Imports());
}