/// <summary>
/// Imports the data from XML file to the table.
/// </summary>
/// <returns></returns>
/// <exception cref="System.Exception">reader</exception>
/// <exception cref="XmlException">Unexpected xml tag + reader.LocalName</exception>
private static void ImportDataToTable(WTable table)
{
FileStream fs = new FileStream(@"../../../Suppliers.xml", FileMode.Open, FileAccess.Read);
XmlReader reader = XmlReader.Create(fs);
if (reader == null)
{
throw new Exception("reader");
}
while (reader.NodeType != XmlNodeType.Element)
{
reader.Read();
}
if (reader.LocalName != "SuppliersList")
{
throw new XmlException("Unexpected xml tag " + reader.LocalName);
}
reader.Read();
while (reader.NodeType == XmlNodeType.Whitespace)
{
reader.Read();
}
while (reader.LocalName != "SuppliersList")
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.LocalName)
{
case "Suppliers":
//Adds new row to the table for importing data from next record.
WTableRow tableRow = table.AddRow(true);
ImportDataToRow(reader, tableRow);
break;
}
}
else
{
reader.Read();
if ((reader.LocalName == "SuppliersList") && reader.NodeType == XmlNodeType.EndElement)
{
break;
}
}
}
reader.Dispose();
fs.Dispose();
}
//Exporting data to word
public void ExportToWord()
{
//A new document is created.
WordDocument document = new WordDocument();
//Adding new table to the document
WTable doctable = new WTable(document);
//Adding a new section to the document.
WSection section = document.AddSection() as WSection;
//Set Margin of the section
section.PageSetup.Margins.All = 72;
//Set page size of the section
section.PageSetup.PageSize = new SizeF(800, 792);
//Create Paragraph styles
WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle;
style.CharacterFormat.FontName = "Calibri";
style.CharacterFormat.FontSize = 11f;
WCharacterFormat charFormat = new WCharacterFormat(document);
charFormat.TextColor = System.Drawing.Color.White;
charFormat.Bold = true;
doctable.AddRow(true, false);
WTableCell cell = new WTableCell(document);
cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat);
cell.Width = 90;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat);
cell.Width = 90;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat);
cell.Width = 90;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat);
cell.Width = 180;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat);
cell.Width = 90;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat);
cell.Width = 90;
doctable.Rows[0].Cells.Add(cell);
//Reading each row from the fetched result
for (int i = 0; i < result.Count(); i++)
{
HiveRecord records = result[i];
doctable.AddRow(true, false);
//Reading each field from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Adding new cell to the document
cell = new WTableCell(document);
//Adding each field to the cell
cell.AddParagraph().AppendText(fields.ToString());
if (j == 3)
{
cell.Width = 180;
}
else
{
cell.Width = 90;
}
//Adding cell to the table
doctable.Rows[i + 1].Cells.Add(cell);
doctable.Rows[0].Cells[j].CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
}
}
//Adding table to the section
section.Tables.Add(doctable);
//Save as word 2003 format
if (rdbWord2003.IsChecked.Value)
{
//Saving the document to disk.
document.Save("Sample.doc");
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.doc");
//Exit
this.Close();
}
}
//Save as word 2007 format
else if (rdbWord2007.IsChecked.Value)
{
//Saving the document as .docx
document.Save("Sample.docx", FormatType.Word2007);
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.docx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Word 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
//Save as word 2010 format
else if (rdbWord2010.IsChecked.Value)
{
//Saving the document as .docx
document.Save("Sample.docx", FormatType.Word2010);
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.docx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Word 2010 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
//Save as word 2013 format
else if (rdbWord2013.IsChecked.Value)
{
//Saving the document as .docx
document.Save("Sample.docx", FormatType.Word2013);
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the MS Word document?", "Document has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the MS Word file using the default Application.[MS Word Or Free WordViewer]
System.Diagnostics.Process.Start("Sample.docx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Word 2013 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
else
{
// Exit
this.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
ErrorMessage.InnerText = "";
if (hdnGroup.Value == "Word")
{
CheckFileStatus FileStatus = new CheckFileStatus();
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Create a new document
WordDocument document = new WordDocument();
//Adding new table to the document
WTable doctable = new WTable(document);
//Adding a new section to the document.
WSection section = document.AddSection() as WSection;
//Set Margin of the section
section.PageSetup.Margins.All = 72;
//Set page size of the section
section.PageSetup.PageSize = new SizeF(800, 792);
//Create Paragraph styles
WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle;
style.CharacterFormat.FontName = "Calibri";
style.CharacterFormat.FontSize = 11f;
//Create a character format for declaring font color and style for the text inside the cell
WCharacterFormat charFormat = new WCharacterFormat(document);
charFormat.TextColor = System.Drawing.Color.White;
charFormat.Bold = true;
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
//To initialize a Hive server connection with Azure cluster
//HqlConnection con = new HqlConnection("<FQDN name of Azure cluster>", 8004, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in HiveResultSet
HiveResultSet result = reader.FetchResult();
//Adding headertext for the table
doctable.AddRow(true, false);
//Creating new cell
WTableCell cell = new WTableCell(document);
cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat);
cell.Width = 75;
//Adding cell to the row
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat);
cell.Width = 75;
doctable.Rows[0].Cells.Add(cell);
//Reading each row from the fetched result
for (int i = 0; i < result.Count(); i++)
{
HiveRecord records = result[i];
doctable.AddRow(true, false);
//Reading each data from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Adding new cell to the document
cell = new WTableCell(document);
//Adding each data to the cell
cell.AddParagraph().AppendText(fields.ToString());
cell.Width = 75;
//Adding cell to the table
doctable.Rows[i + 1].Cells.Add(cell);
doctable.Rows[0].Cells[j].CellFormat.BackColor = Color.FromArgb(51, 153, 51);
}
}
//Adding table to the section
section.Tables.Add(doctable);
//Save as word 2007 format
if (rBtnWord2003.Checked == true)
{
document.Save("Sample.doc", FormatType.Doc, Response, HttpContentDisposition.Attachment);
}
else if (rBtnWord2007.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2007, Response, HttpContentDisposition.Attachment);
}
//Save as word 2010 format
else if (rbtnWord2010.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2010, Response, HttpContentDisposition.Attachment);
}
//Save as word 2013 format
else if (rbtnWord2013.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2013, Response, HttpContentDisposition.Attachment);
}
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
else if (hdnGroup.Value == "Excel")
{
ErrorMessage.InnerText = "";
CheckFileStatus FileStatus = new CheckFileStatus();
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hive samples" }, StringSplitOptions.None));
ErrorMessage.InnerText = FileStatus.CheckFile(path);
if (ErrorMessage.InnerText == "")
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
//Adding header text for worksheet
worksheet[1, 1].Text = "ContactID";
worksheet[1, 2].Text = "FullName";
worksheet[1, 3].Text = "Age";
worksheet[1, 4].Text = "EmailAddress";
worksheet[1, 5].Text = "PhoneNo";
worksheet[1, 6].Text = "ModifiedDate";
//Initializing the hive server connection
HqlConnection con = new HqlConnection("localhost", 10000, HiveServer.HiveServer2);
//To initialize a Hive server connection with secured cluster
//HqlConnection con = new HqlConnection("<Secured cluster Namenode IP>", 10000, HiveServer.HiveServer2,"<username>","<password>");
con.Open();
//Create table for adventure person contacts
HqlCommand createCommand = new HqlCommand("CREATE EXTERNAL TABLE IF NOT EXISTS AdventureWorks_Person_Contact(ContactID int,FullName string,Age int,EmailAddress string,PhoneNo string,ModifiedDate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/Data/AdventureWorks'", con);
createCommand.ExecuteNonQuery();
//Passing the hive query
HqlCommand command = new HqlCommand("Select * from AdventureWorks_Person_Contact", con);
//Executing the query
HqlDataReader reader = command.ExecuteReader();
reader.FetchSize = 100;
//Fetches the result from the reader and store it in a seperate set
HiveResultSet result = reader.FetchResult();
//Reading each row from the fetched result
for (int i = 0; i < result.Count(); i++)
{
HiveRecord records = result[i];
//Reading each field from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Assigning each field value to the worksheet based on index
worksheet[i + 2, j + 1].Text = fields.ToString();
}
}
worksheet.Range["A1:F1"].CellStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White;
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(51, 153, 51);
worksheet.UsedRange.AutofitColumns();
//Saving workbook on user relevant name
string fileName = "Sample.xlsx";
//conditions for selecting version
//Save as Excel 97to2003 format
if (rBtn2003.Checked == true)
{
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs("Sample.xls", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2007 formt
else if (rBtn2007.Checked == true)
{
workbook.Version = ExcelVersion.Excel2007;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2010 format
else if (rbtn2010.Checked == true)
{
workbook.Version = ExcelVersion.Excel2010;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//Save as Excel 2013 format
else if (rbtn2013.Checked == true)
{
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog);
}
//closing the workwook
workbook.Close();
//Closing the excel engine
excelEngine.Dispose();
//Closing the hive connection
con.Close();
}
catch (HqlConnectionException)
{
ErrorMessage.InnerText = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
}
}
public ActionResult ExportDefault(string SaveOption)
{
if (SaveOption == null)
{
ErrorMessage = "";
return(View());
}
else if (SaveOption == "Excel 2013" || SaveOption == "Excel 2010" || SaveOption == "Excel 2007" || SaveOption == "Excel 2003" || SaveOption == "CSV")
{
ErrorMessage = "";
string path = new System.IO.DirectoryInfo(Request.PhysicalPath + "..\\..\\..\\..\\..\\..\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv").FullName;
CheckFileStatus CheckFile = new CheckFileStatus();
ErrorMessage = CheckFile.CheckFile(path);
if (ErrorMessage == "")
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
var result = CustomersData.list();
ErrorMessage = null;
//Adding header text for worksheet
worksheet[1, 1].Text = "Contact Id";
worksheet[1, 2].Text = "Full Name";
worksheet[1, 3].Text = "Age";
worksheet[1, 4].Text = "Phone Number";
worksheet[1, 5].Text = "Email Id";
worksheet[1, 6].Text = "Modified Date";
int i = 1;
//Reading each row from the fetched result
foreach (PersonDetail records in result)
{
//Reading each data from the row
//Assigning each data to the worksheet based on index
worksheet[i + 1, 1].Text = records.ContactId;
worksheet[i + 1, 2].Text = records.FullName;
worksheet[i + 1, 3].Text = records.Age;
worksheet[i + 1, 4].Text = records.PhoneNumber;
worksheet[i + 1, 5].Text = records.EmailId;
worksheet[i + 1, 6].Text = records.ModifiedDate;
i++;
}
//Assigning Header text with cell color
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(51, 153, 51);
worksheet.Range["A1:F1"].CellStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White;
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.UsedRange.AutofitColumns();
#endregion
//Save as .xls format
if (SaveOption == "Excel 2003")
{
return(excelEngine.SaveAsActionResult(workbook, "Sample.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
//Save as .xlsx format
else if (SaveOption == "Excel 2007")
{
workbook.Version = ExcelVersion.Excel2007;
return(excelEngine.SaveAsActionResult(workbook, "Sample.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2007));
}
//Save as .xlsx format
else if (SaveOption == "Excel 2010")
{
workbook.Version = ExcelVersion.Excel2010;
return(excelEngine.SaveAsActionResult(workbook, "Sample.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010));
}
//Save as .xlsx format
else if (SaveOption == "Excel 2013")
{
workbook.Version = ExcelVersion.Excel2013;
return(excelEngine.SaveAsActionResult(workbook, "Sample.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2013));
}
//Save as .csv format
else if (SaveOption == "CSV")
{
return(excelEngine.SaveAsActionResult(workbook, "Sample.csv", ",", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.CSV));
}
//Close the workbook
workbook.Close();
//Close the excelengine
excelEngine.Dispose();
}
catch (HqlConnectionException)
{
ErrorMessage = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
return(View());
}
else
{
ErrorMessage = "";
string path = new System.IO.DirectoryInfo(Request.PhysicalPath + "..\\..\\..\\..\\..\\..\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv").FullName;
CheckFileStatus CheckFile = new CheckFileStatus();
ErrorMessage = CheckFile.CheckFile(path);
//A new document is created.
if (ErrorMessage == "")
{
try
{
WordDocument document = new WordDocument();
//Adding new table to the document
WTable doctable = new WTable(document);
//Adding a new section to the document.
WSection section = document.AddSection() as WSection;
//Set Margin of the section
section.PageSetup.Margins.All = 72;
//Set page size of the section
section.PageSetup.PageSize = new SizeF(800, 792);
//Create Paragraph styles
WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle;
style.CharacterFormat.FontName = "Calibri";
style.CharacterFormat.FontSize = 11f;
//Reading the data from the table
var result = CustomersData.list();
doctable.AddRow(true, false);
//creating new cell
WTableCell cell = new WTableCell(document);
//Create a character format for declaring font color and style for the text inside the cell
WCharacterFormat charFormat = new WCharacterFormat(document);
charFormat.TextColor = System.Drawing.Color.White;
charFormat.Bold = true;
//Adding header text for the table
cell.AddParagraph().AppendText("Customer Id").ApplyCharacterFormat(charFormat);
cell.Width = 75;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
//Adding cell to rows
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Full Name").ApplyCharacterFormat(charFormat);
cell.Width = 90;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Age").ApplyCharacterFormat(charFormat);
cell.Width = 75;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Phone Number").ApplyCharacterFormat(charFormat);
cell.Width = 90;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Email Id").ApplyCharacterFormat(charFormat);
cell.Width = 180;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("Modified Date").ApplyCharacterFormat(charFormat);
cell.Width = 125;
cell.CellFormat.BackColor = System.Drawing.Color.FromArgb(51, 153, 51);
doctable.Rows[0].Cells.Add(cell);
int i = 1;
//Reading each row from the fetched result
foreach (PersonDetail records in result)
{
doctable.AddRow(true, false);
//Assigning fetched result to the cell
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.ContactId.ToString());
cell.Width = 75;
doctable.Rows[i].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.FullName.ToString());
cell.Width = 90;
doctable.Rows[i].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.Age.ToString());
cell.Width = 75;
doctable.Rows[i].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.PhoneNumber.ToString());
cell.Width = 90;
doctable.Rows[i].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.EmailId.ToString());
cell.Width = 180;
doctable.Rows[i].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText(records.ModifiedDate.ToString());
cell.Width = 125;
doctable.Rows[i].Cells.Add(cell);
i++;
}
//Adding table to the section
section.Tables.Add(doctable);
#region saveOption
//Save as .doc Word 97-2003 format
if (SaveOption == "Word 97-2003")
{
return(document.ExportAsActionResult("Sample.doc", FormatType.Doc, HttpContext.ApplicationInstance.Response, HttpContentDisposition.Attachment));
}
//Save as .docx Word 2007 format
else if (SaveOption == "Word 2007")
{
return(document.ExportAsActionResult("Sample.docx", FormatType.Word2007, HttpContext.ApplicationInstance.Response, HttpContentDisposition.Attachment));
}
//Save as .docx Word 2010 format
else if (SaveOption == "Word 2010")
{
return(document.ExportAsActionResult("Sample.docx", FormatType.Word2010, HttpContext.ApplicationInstance.Response, HttpContentDisposition.Attachment));
}
//Save as .docx Word 2013 format
else if (SaveOption == "Word 2013")
{
return(document.ExportAsActionResult("Sample.docx", FormatType.Word2013, HttpContext.ApplicationInstance.Response, HttpContentDisposition.Attachment));
}
#endregion saveOption
}
catch (HqlConnectionException)
{
ErrorMessage = "Could not establish a connection to the HiveServer. Please run HiveServer2 from the Syncfusion service manager dashboard.";
}
}
return(View());
}
}
protected void Button1_Click(object sender, EventArgs e)
{
ErrorMessage.InnerText = "";
if (hdnGroup.Value == "Word")
{
string path = string.Format("{0}\\..\\Data\\AdventureWorks\\AdventureWorks_Person_Contact.csv", Request.PhysicalPath.ToLower().Split(new string[] { "\\c# hbase samples" }, StringSplitOptions.None));
try
{
//Create a new document
WordDocument document = new WordDocument();
//Adding new table to the document
WTable doctable = new WTable(document);
//Adding a new section to the document.
WSection section = document.AddSection() as WSection;
//Set Margin of the section
section.PageSetup.Margins.All = 72;
//Set page size of the section
section.PageSetup.PageSize = new SizeF(800, 792);
//Create Paragraph styles
WParagraphStyle style = document.AddParagraphStyle("Normal") as WParagraphStyle;
style.CharacterFormat.FontName = "Calibri";
style.CharacterFormat.FontSize = 11f;
//Create a character format for declaring font color and style for the text inside the cell
WCharacterFormat charFormat = new WCharacterFormat(document);
charFormat.TextColor = System.Drawing.Color.White;
charFormat.Bold = true;
#region creating connection
HBaseConnection con = new HBaseConnection("localhost", 10003);
con.Open();
#endregion creating connection
#region parsing csv input file
csv csvObj = new csv();
object[,] cells;
cells = null;
cells = csvObj.Table(path, false, ',');
#endregion parsing csv input file
#region creating table
String tableName = "AdventureWorks_Person_Contact";
List <string> columnFamilies = new List <string>();
columnFamilies.Add("info");
columnFamilies.Add("contact");
columnFamilies.Add("others");
if (!HBaseOperation.IsTableExists(tableName, con))
{
if (columnFamilies.Count > 0)
{
HBaseOperation.CreateTable(tableName, columnFamilies, con);
}
else
{
throw new HBaseException("ERROR: Table must have at least one column family");
}
}
# endregion
#region Inserting Values
string[] column = new string[] { "CONTACTID", "FULLNAME", "AGE", "EMAILID", "PHONE", "MODIFIEDDATE" };
Dictionary <string, IList <HMutation> > rowCollection = new Dictionary <string, IList <HMutation> >();
string rowKey;
for (int i = 0; i < cells.GetLength(0); i++)
{
List <HMutation> mutations = new List <HMutation>();
rowKey = cells[i, 0].ToString();
for (int j = 1; j < column.Length; j++)
{
HMutation mutation = new HMutation();
mutation.ColumnFamily = j < 3 ? "info" : j < 5 ? "contact" : "others";
mutation.ColumnName = column[j];
mutation.Value = cells[i, j].ToString();
mutations.Add(mutation);
}
rowCollection[rowKey] = mutations;
}
HBaseOperation.InsertRows(tableName, rowCollection, con);
#endregion Inserting Values
#region scan values
HBaseOperation.FetchSize = 100;
HBaseResultSet table = HBaseOperation.ScanTable(tableName, con);
//Adding headertext for the table
doctable.AddRow(true, false);
//Creating new cell
WTableCell cell = new WTableCell(document);
cell.AddParagraph().AppendText("ContactId").ApplyCharacterFormat(charFormat);
cell.Width = 100;
//Adding cell to the row
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("contact:EmailId").ApplyCharacterFormat(charFormat);
cell.Width = 200;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("contact:PhoneNo").ApplyCharacterFormat(charFormat);
cell.Width = 150;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("info:Age").ApplyCharacterFormat(charFormat);
cell.Width = 100;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("info:FullName").ApplyCharacterFormat(charFormat);
cell.Width = 150;
doctable.Rows[0].Cells.Add(cell);
cell = new WTableCell(document);
cell.AddParagraph().AppendText("others:ModifiedDate").ApplyCharacterFormat(charFormat);
cell.Width = 200;
doctable.Rows[0].Cells.Add(cell);
//Reading each row from the fetched result
for (int i = 0; i < table.Count(); i++)
{
HBaseRecord records = table[i];
doctable.AddRow(true, false);
//Reading each data from the row
for (int j = 0; j < records.Count; j++)
{
Object fields = records[j];
//Adding new cell to the document
cell = new WTableCell(document);
//Adding each data to the cell
cell.AddParagraph().AppendText(fields.ToString());
if (j != 1 && j != 2 && j != 4 && j != 5)
{
cell.Width = 100;
}
else if (j == 2 || j == 4)
{
cell.Width = 150;
}
else
{
cell.Width = 200;
}
//Adding cell to the table
doctable.Rows[i + 1].Cells.Add(cell);
doctable.Rows[0].Cells[j].CellFormat.BackColor = Color.FromArgb(51, 153, 51);
}
}
//Adding table to the section
section.Tables.Add(doctable);
//Save as word 2007 format
if (rBtnWord2003.Checked == true)
{
document.Save("Sample.doc", FormatType.Doc, Response, HttpContentDisposition.Attachment);
}
else if (rBtnWord2007.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2007, Response, HttpContentDisposition.Attachment);
}
//Save as word 2010 format
else if (rbtnWord2010.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2010, Response, HttpContentDisposition.Attachment);
}
//Save as word 2013 format
else if (rbtnWord2013.Checked == true)
{
document.Save("Sample.docx", FormatType.Word2013, Response, HttpContentDisposition.Attachment);
}
#endregion scan values
#region close connection
//Closing the hive connection
con.Close();
#endregion close connection
}