public DataTable ProcesAttachment(MailAtachment atachFile)
{
DataTable atachData = null;
// System.Windows.Forms.MessageBox.Show(atachFile.Text);
if (atachFile.file.Substring(atachFile.file.Length - 3, 3).ToLower() == "dbf")
{
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseando DBF");
Evento.Save();
atachData = ParseDBF.ReadDBF(atachFile.data);
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseado Exitoso");
Evento.Save();
//System.Windows.Forms.MessageBox.Show(atachData.Columns.Count.ToString() + "dbf");
}
if (atachFile.file.Substring(atachFile.file.Length - 3, 3).ToLower() == "xls" || atachFile.file.Substring(atachFile.file.Length - 4, 4).ToLower() == "xlsx")
{
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseando Excel");
Evento.Save();
ExcelDataReader.ExcelDataReader rd;
rd = new ExcelDataReader.ExcelDataReader(atachFile.data);
atachData = rd.TruColumnsTransform(rd.WorkbookData.Tables[0]);
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseado Exitoso");
Evento.Save();
// System.Windows.Forms.MessageBox.Show(atachData.Columns.Count.ToString() + "Excel");
}
if (atachFile.file.Substring(atachFile.file.Length - 3, 3).ToLower() == "xml")
{
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseando Xml");
Evento.Save();
atachData = ParseXml(atachFile.data);
Evento.SetEvento(DateTime.Now, DateTime.Now, "Parseado Exitoso");
Evento.Save();
}
if (atachFile.file.Substring(atachFile.file.Length - 4, 4).ToLower() == "gzip")
{
Evento.SetEvento(DateTime.Now, DateTime.Now, "Descomprimiendo");
Evento.Save();
MemoryStream input = new MemoryStream();
input.Write(atachFile.data, 0, atachFile.data.Length);
input.Seek(0, SeekOrigin.Begin);
GZipStream gzip = new GZipStream(input, CompressionMode.Decompress, true);
MemoryStream output = new MemoryStream();
byte[] buff = new byte[64];
int read = -1;
read = gzip.Read(buff, 0, buff.Length);
while (read > 0)
{
output.Write(buff, 0, read);
read = gzip.Read(buff, 0, buff.Length);
}
gzip.Close();
Evento.SetEvento(DateTime.Now, DateTime.Now, "Descomprimiendo Exitoso");
Evento.Save();
atachData = ParseXml(output.ToArray());
}
return(atachData);
}
// 05/06/2011 We need to be able to distinguish between Excel 2003 and Excel 2007.
public static XmlDocument ConvertStreamToXml(string sImportModule, string sSourceType, string sCustomDelimiterValue, Stream stm, string sFILE_EXT)
{
XmlDocument xml = new XmlDocument();
xml.AppendChild(xml.CreateProcessingInstruction("xml" , "version=\"1.0\" encoding=\"UTF-8\""));
xml.AppendChild(xml.CreateElement("xml"));
switch ( sSourceType )
{
case "xmlspreadsheet":
{
xml.Load(stm);
xml = ConvertXmlSpreadsheetToXml(xml, sImportModule.ToLower());
break;
}
case "xml":
{
// 10/10/2006 Don't require that the file end in XML in order to be imported as a XML document.
// sFILE_MIME_TYPE == "text/xml"
// 10/10/2006 The reason we use a memory stream to load an XML file is to give us the opportunity to fix the MySQL data file.
// MySQL stores binary 0s and 1s for bit values, and we need them to be text 0s and 1s.
using ( MemoryStream mstm = new MemoryStream() )
{
using ( BinaryWriter mwtr = new BinaryWriter(mstm) )
{
using ( BinaryReader reader = new BinaryReader(stm) )
{
byte[] binBYTES = reader.ReadBytes(8 * 1024);
while ( binBYTES.Length > 0 )
{
for ( int i = 0; i < binBYTES.Length; i++ )
{
// MySQL dump seems to dump binary 0 & 1 for byte values.
if ( binBYTES[i] == 0 )
mstm.WriteByte(Convert.ToByte('0'));
else if ( binBYTES[i] == 1 )
mstm.WriteByte(Convert.ToByte('1'));
else
mstm.WriteByte(binBYTES[i]);
}
binBYTES = reader.ReadBytes(8 * 1024);
}
}
mwtr.Flush();
mstm.Seek(0, SeekOrigin.Begin);
xml.Load(mstm);
bool bExcelSheet = false;
foreach ( XmlNode xNode in xml )
{
if ( xNode.NodeType == XmlNodeType.ProcessingInstruction )
{
if ( xNode.Name == "mso-application" && xNode.InnerText == "progid=\"Excel.Sheet\"" )
{
bExcelSheet = true;
break;
}
}
}
if ( bExcelSheet )
xml = ConvertXmlSpreadsheetToXml(xml, sImportModule.ToLower());
}
}
break;
}
case "excel":
{
// 05/06/2011 Use the OpenXML library to read an Excel 2007 xlsx file.
if ( sFILE_EXT.ToLower() == ".xlsx" )
{
xml = Excel2007Reader.ConvertSpreadsheetToXml(stm, sImportModule.ToLower());
}
else
{
ExcelDataReader.ExcelDataReader spreadsheet = new ExcelDataReader.ExcelDataReader(stm);
if ( spreadsheet.WorkbookData.Tables.Count > 0 )
{
xml = ConvertTableToXml(spreadsheet.WorkbookData.Tables[0], sImportModule.ToLower());
}
}
break;
}
case "other_tab":
{
CsvDataReader spreadsheet = new CsvDataReader(stm, ControlChars.Tab);
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
case "custom_delimited":
{
// 10/10/2006 We are only going to allow a single character separator for now.
if ( sCustomDelimiterValue.Length == 0 )
sCustomDelimiterValue = ",";
CsvDataReader spreadsheet = new CsvDataReader(stm, sCustomDelimiterValue[0]);
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
case "dbase":
{
// 02/24/2010 Test the dBase driver so that we can provide a good error message.
try
{
string sTempPath = Path.GetTempPath();
using ( OdbcConnection con = new OdbcConnection() )
{
con.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + sTempPath + ";Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
con.Open();
}
}
catch(Exception ex)
{
throw(new Exception("There was a problem loading the Microsoft dBase Driver. If you are running a 64-bit OS, then you will need to set the Enable 32-bit Applications flag in IIS. " + ex.Message));
}
xml = ConvertDBaseToXml(stm, sImportModule.ToLower(), false, null);
break;
}
case "act":
{
// 02/24/2010 Test the dBase driver so that we can provide a good error message.
try
{
string sTempPath = Path.GetTempPath();
using ( OdbcConnection con = new OdbcConnection() )
{
con.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + sTempPath + ";Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
con.Open();
}
}
catch(Exception ex)
{
throw(new Exception("There was a problem loading the Microsoft dBase Driver. If you are running a 64-bit OS, then you will need to set the Enable 32-bit Applications flag in IIS. " + ex.Message));
}
xml = ACTImport.ConvertActToXml(sImportModule, stm);
break;
}
default:
{
// 08/21/2006 Everything else is comma separated. Convert to XML.
CsvDataReader spreadsheet = new CsvDataReader(stm, ',');
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
}
return xml;
}
private void TBCreateSpreadsheet_Click(object sender, EventArgs e)
{
try
{
if (openDialog.ShowDialog() != DialogResult.OK) return;
//grpData.Enabled = false;
string filename = openDialog.FileName;
FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);
spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
fs.Close();
if (spreadsheet.WorkbookData.Tables.Count > 0)
{
switch (string.IsNullOrEmpty(Base.DBName))
{
case true:
MessageBox.Show("Create or select a database.", "DATABASE NEEDED!", MessageBoxButtons.OK);
break;
case false:
saveTheSpreadSheetToTheDatabase();
MessageBox.Show("Successfully Uploaded.", "Information", MessageBoxButtons.OK);
break;
default:
break;
}
}
//cboSheet.Items.Clear();
//cboSheet.DisplayMember = "TableName";
//foreach (DataTable dt in spreadsheet.WorkbookData.Tables)
// cboSheet.Items.Add(dt);
//if (cboSheet.Items.Count == 0) return;
//grpData.Enabled = true;
//checker = true;
//cboSheet.SelectedIndex = 0;
//btnSave.Visible = true;
//lblSheet.Visible = true;
//cboSheet.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("Failed to read file: \n" + ex.Message);
}
}
private void importTheSheet(string importFilename)
{
string path = BusinessLanguage.InputDirectory + Base.DBName;
try
{
// Try to create the directory.
DirectoryInfo di = Directory.CreateDirectory(path);
string filename = BusinessLanguage.InputDirectory + Base.DBName + importFilename;
bool fileCheck = BusinessLanguage.checkIfFileExists(filename);
if (fileCheck)
{
FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);
spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
fs.Close();
//If the file was SURVEY, all sections production data will be on this datatable.
//Only the selected section's data must be saved.
saveTheSpreadSheetToTheDatabase();
}
else
{
MessageBox.Show("File " + filename + " - does not exist", "Check", MessageBoxButtons.OK);
}
//Check if file exists
//If not = Message
//If exists ==> Import
}
catch
{
MessageBox.Show("File " + importFilename + " - is inuse by another package?", "Check", MessageBoxButtons.OK);
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
try
{
if ( e.CommandName == "Next" )
{
switch ( nImportStep )
{
case 1:
{
nImportStep++;
ShowStep();
ViewState["ImportStep"] = nImportStep;
break;
}
case 2:
{
if ( Page.IsValid )
{
HttpPostedFile pstIMPORT = fileIMPORT.PostedFile;
if ( pstIMPORT != null )
{
if ( pstIMPORT.FileName.Length > 0 )
{
string sFILENAME = Path.GetFileName (pstIMPORT.FileName);
string sFILE_EXT = Path.GetExtension(sFILENAME);
string sFILE_MIME_TYPE = pstIMPORT.ContentType;
if ( radXML_SPREADSHEET.Checked )
{
xml.Load(pstIMPORT.InputStream);
ConvertXmlSpreadsheetToXml(ref xml, sImportModule.ToLower());
}
else if ( sFILE_MIME_TYPE == "text/xml" || radXML.Checked )
{
using ( MemoryStream mstm = new MemoryStream() )
{
using ( BinaryWriter mwtr = new BinaryWriter(mstm) )
{
using ( BinaryReader reader = new BinaryReader(pstIMPORT.InputStream) )
{
byte[] binBYTES = reader.ReadBytes(8 * 1024);
while ( binBYTES.Length > 0 )
{
for ( int i = 0; i < binBYTES.Length; i++ )
{
// MySQL dump seems to dump binary 0 & 1 for byte values.
if ( binBYTES[i] == 0 )
mstm.WriteByte(Convert.ToByte('0'));
else if ( binBYTES[i] == 1 )
mstm.WriteByte(Convert.ToByte('1'));
else
mstm.WriteByte(binBYTES[i]);
}
binBYTES = reader.ReadBytes(8 * 1024);
}
}
mwtr.Flush();
mstm.Seek(0, SeekOrigin.Begin);
xml.Load(mstm);
bool bExcelSheet = false;
foreach ( XmlNode xNode in xml )
{
if ( xNode.NodeType == XmlNodeType.ProcessingInstruction )
{
if ( xNode.Name == "mso-application" && xNode.InnerText == "progid=\"Excel.Sheet\"" )
{
bExcelSheet = true;
break;
}
}
}
if ( bExcelSheet )
ConvertXmlSpreadsheetToXml(ref xml, sImportModule.ToLower());
}
}
}
else if ( radEXCEL.Checked )
{
ExcelDataReader.ExcelDataReader spreadsheet = new ExcelDataReader.ExcelDataReader(pstIMPORT.InputStream);
if ( spreadsheet.WorkbookData.Tables.Count > 0 )
{
xml = ConvertTableToXml(spreadsheet.WorkbookData.Tables[0], sImportModule.ToLower());
}
}
else if ( radCUSTOM_TAB.Checked )
{
CsvDataReader spreadsheet = new CsvDataReader(pstIMPORT.InputStream, ControlChars.Tab);
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
}
else
{
// 08/21/2006 Paul. Everything else is comma separated. Convert to XML.
CsvDataReader spreadsheet = new CsvDataReader(pstIMPORT.InputStream, ',');
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
}
}
}
}
if ( xml.DocumentElement == null )
throw(new Exception(L10n.Term("Import.LBL_NOTHING")));
// 08/21/2006 Paul. Don't move to next step if there is no data.
XmlNodeList nlRows = xml.DocumentElement.SelectNodes(sImportModule.ToLower());
if ( nlRows.Count == 0 )
throw(new Exception(L10n.Term("Import.LBL_NOTHING")));
ViewState["xml"] = xml.OuterXml;
UpdateImportMappings(true);
nImportStep++ ;
ShowStep();
ViewState["ImportStep"] = nImportStep;
break;
}
case 3:
{
UpdateImportMappings(false);
GenerateImport();
nImportStep++ ;
ShowStep();
ViewState["ImportStep"] = nImportStep;
break;
}
}
}
else if ( e.CommandName == "Back" )
{
if ( nImportStep > 1 )
{
nImportStep--;
ShowStep();
ViewState["ImportStep"] = nImportStep;
}
}
else if ( e.CommandName == "ImportMore" )
{
//radEXCEL.Checked = true;
//chkHasHeader.Checked = true;
//nImportStep = 1;
//ShowStep();
//ViewState["ImportStep"] = nImportStep;
// 08/20/2006 Paul. Redirecting is a safer way to reset all variables.
Response.Redirect(Request.Path);
}
else if ( e.CommandName == "Finish" )
{
Response.Redirect("~/" + sImportModule);
}
}
catch(Exception ex)
{
//SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
return;
}
}
private void TBCreateSpreadsheet_Click(object sender, EventArgs e)
{
try
{
if (openDialog.ShowDialog() != DialogResult.OK) return;
//grpData.Enabled = false;
string filename = openDialog.FileName;
FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);
spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
fs.Close();
if (spreadsheet.WorkbookData.Tables.Count > 0)
{
switch (string.IsNullOrEmpty(Base.DBName))
{
case true:
MessageBox.Show("Create or select a database.", "DATABASE NEEDED!", MessageBoxButtons.OK);
break;
case false:
saveTheSpreadSheetToTheDatabase();
MessageBox.Show("Successfully Uploaded.", "Information", MessageBoxButtons.OK);
break;
default:
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to read file: \n" + ex.Message);
}
}
public static XmlDocument ConvertStreamToXml(string sImportModule, string sSourceType, string sCustomDelimiterValue, Stream stm)
{
XmlDocument xml = new XmlDocument();
switch ( sSourceType )
{
case "xmlspreadsheet":
{
xml.Load(stm);
xml = ConvertXmlSpreadsheetToXml(xml, sImportModule.ToLower());
break;
}
case "xml":
{
// 10/10/2006 Paul. Don't require that the file end in XML in order to be imported as a XML document.
// sFILE_MIME_TYPE == "text/xml"
// 10/10/2006 Paul. The reason we use a memory stream to load an XML file is to give us the opportunity to fix the MySQL data file.
// MySQL stores binary 0s and 1s for bit values, and we need them to be text 0s and 1s.
using ( MemoryStream mstm = new MemoryStream() )
{
using ( BinaryWriter mwtr = new BinaryWriter(mstm) )
{
using ( BinaryReader reader = new BinaryReader(stm) )
{
byte[] binBYTES = reader.ReadBytes(8 * 1024);
while ( binBYTES.Length > 0 )
{
for ( int i = 0; i < binBYTES.Length; i++ )
{
// MySQL dump seems to dump binary 0 & 1 for byte values.
if ( binBYTES[i] == 0 )
mstm.WriteByte(Convert.ToByte('0'));
else if ( binBYTES[i] == 1 )
mstm.WriteByte(Convert.ToByte('1'));
else
mstm.WriteByte(binBYTES[i]);
}
binBYTES = reader.ReadBytes(8 * 1024);
}
}
mwtr.Flush();
mstm.Seek(0, SeekOrigin.Begin);
xml.Load(mstm);
bool bExcelSheet = false;
foreach ( XmlNode xNode in xml )
{
if ( xNode.NodeType == XmlNodeType.ProcessingInstruction )
{
if ( xNode.Name == "mso-application" && xNode.InnerText == "progid=\"Excel.Sheet\"" )
{
bExcelSheet = true;
break;
}
}
}
if ( bExcelSheet )
xml = ConvertXmlSpreadsheetToXml(xml, sImportModule.ToLower());
}
}
break;
}
case "excel":
{
ExcelDataReader.ExcelDataReader spreadsheet = new ExcelDataReader.ExcelDataReader(stm);
if ( spreadsheet.WorkbookData.Tables.Count > 0 )
{
xml = ConvertTableToXml(spreadsheet.WorkbookData.Tables[0], sImportModule.ToLower());
}
break;
}
case "other_tab":
{
CsvDataReader spreadsheet = new CsvDataReader(stm, ControlChars.Tab);
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
case "custom_delimited":
{
// 10/10/2006 Paul. We are only going to allow a single character separator for now.
if ( sCustomDelimiterValue.Length == 0 )
sCustomDelimiterValue = ",";
CsvDataReader spreadsheet = new CsvDataReader(stm, sCustomDelimiterValue[0]);
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
default:
{
// 08/21/2006 Paul. Everything else is comma separated. Convert to XML.
CsvDataReader spreadsheet = new CsvDataReader(stm, ',');
if ( spreadsheet.Table != null )
{
xml = ConvertTableToXml(spreadsheet.Table, sImportModule.ToLower());
}
break;
}
}
return xml;
}
