<< Click to Display Table of Contents >> Navigation: Developers' Guide > Using Data Interface > FAQs > Sample > Creating an Excel File |
Navigation: Developers' Guide > Using Data Interface > FAQs > Sample >
Creating an Excel File
This topic demonstrates how to create an Excel file.
using System; using System.Collections.Generic; using System.ComponentModel; using System.Drawing; using System.Data; using System.Text; using System.Linq; using System.Threading.Tasks; using System.Windows.Forms; using DevExpress.XtraEditors; using CXS.Retail.UIComponents; using CXS.Retail.ManagementUIComponents; using CXS.Platform.Data; using CXS.Retail.Extensibility; using CXS.Retail.BusinessLogic; using CXS.SubSystem.Customer; using CXS.Platform.UIComponents; using CXS.Platform.Core;
namespace CustomScreenSampleForMC { public partial class SampleAddEditView : BaseConsoleCustomView { private Int64 m_SampleUDTKey = 0; private Boolean m_EditMode = false; private Boolean m_ViewMode = false; Image logoImage = null; DevExpress.XtraEditors.CheckEdit chkActive = null; /// <summary> /// Interface of SammpleService /// </summary> private ISampleService sampleService;
private void ExportButtonClickHandler(object sender, System.EventArgs e) { IList objList = new ArrayList();
objList = (IList)m_VerticalGridAddEditScreen.DataSource;
DataTable source = new DataTable("SampleUDT"); source.Columns.Add("LensTypeKey"); source.Columns.Add("U_Id"); source.Columns.Add("U_Description"); source.Columns.Add("U_Type"); source.Columns.Add("U_CustomLookup"); source.Columns.Add("U_SystemLookup"); source.Columns.Add("U_IsActive"); source.Columns.Add("U_BalanceAmount"); source.Columns.Add("U_IsDeleted");
if (objList != null && objList.Count > 0) { foreach (SampleModel item in objList) { if (!(String.IsNullOrEmpty(item.U_Id))) { FillData(objList, source); String SheetName = "SampleUDTExportFile"; System.IO.StreamWriter excelDoc = new StreamWriter(Environment.CurrentDirectory.ToString() + @"\SampleUDTExportFile.xslx"); try { const string startExcelXML = "<xml version>\r\n<Workbook " + "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" + " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " + "xmlns:x=\"urn:schemas- microsoft-com:office:" + "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" + "office:spreadsheet\">\r\n <Styles>\r\n " + "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" + "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" + "\r\n <Protection/>\r\n </Style>\r\n " + "<Style ss:ID=\"BoldColumn\">\r\n <Font " + "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " + "<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" + " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " + "ss:ID=\"Decimal\">\r\n <NumberFormat " + "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " + "<Style ss:ID=\"Integer\">\r\n <NumberFormat " + "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " + "ss:ID=\"DateLiteral\">\r\n <NumberFormat " + "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + "</Styles>\r\n ";
const string endExcelXML = "</Workbook>";
int rowCount = 0; int sheetCount = 1;
excelDoc.Write(startExcelXML); excelDoc.Write("<Worksheet ss:Name=\"" + SheetName + "\">"); excelDoc.Write("<Table>"); excelDoc.Write("<Row>"); for (int x = 0; x < source.Columns.Count; x++) { excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">"); excelDoc.Write(source.Columns[x].ColumnName); excelDoc.Write("</Data></Cell>"); } excelDoc.Write("</Row>"); foreach (DataRow x in source.Rows) { rowCount++; //if the number of rows is > 64000 create a new page to continue output if (rowCount == 64000) { rowCount = 0; sheetCount++; excelDoc.Write("</Table>"); excelDoc.Write(" </Worksheet>"); excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">"); excelDoc.Write("<Table>"); } excelDoc.Write("<Row>"); for (int y = 0; y < source.Columns.Count; y++) { System.Type rowType; rowType = x[y].GetType(); switch (rowType.ToString()) { case "System.String": string XMLstring = x[y].ToString(); XMLstring = XMLstring.Trim(); XMLstring = XMLstring.Replace("&", "&"); XMLstring = XMLstring.Replace(">", ">"); XMLstring = XMLstring.Replace("<", "<"); excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); excelDoc.Write(XMLstring); excelDoc.Write("</Data></Cell>"); break; case "System.DateTime": DateTime XMLDate = (DateTime)x[y]; string XMLDatetoString = ""; //Excel Converted Date XMLDatetoString = XMLDate.Year.ToString() + "-" + (XMLDate.Month < 10 ? "0" + XMLDate.Month.ToString() : XMLDate.Month.ToString()) + "-" + (XMLDate.Day < 10 ? "0" + XMLDate.Day.ToString() : XMLDate.Day.ToString()) + "T" + (XMLDate.Hour < 10 ? "0" + XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) + ":" + (XMLDate.Minute < 10 ? "0" + XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) + ":" + (XMLDate.Second < 10 ? "0" + XMLDate.Second.ToString() : XMLDate.Second.ToString()) + ".000"; excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + "<Data ss:Type=\"DateTime\">"); excelDoc.Write(XMLDatetoString); excelDoc.Write("</Data></Cell>"); break; case "System.Boolean": excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); excelDoc.Write(x[y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": excelDoc.Write("<Cell ss:StyleID=\"Integer\">" + "<Data ss:Type=\"Number\">"); excelDoc.Write(x[y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Decimal": case "System.Double": excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" + "<Data ss:Type=\"Number\">"); excelDoc.Write(x[y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.DBNull": excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); excelDoc.Write(""); excelDoc.Write("</Data></Cell>"); break; default: throw (new Exception(rowType.ToString() + " not handled.")); } } excelDoc.Write("</Row>"); } excelDoc.Write("</Table>"); excelDoc.Write(" </Worksheet>");
excelDoc.Write(endExcelXML); excelDoc.Close();
ShowInformation(Common.GetString("SampleAddEditView", "RecordExportedSuccessfully")); } catch { if (excelDoc != null) { excelDoc.Close(); excelDoc = null; } ShowError(Common.GetString("SampleAddEditView", "ErrorEncountered")); } finally { if (excelDoc != null) { excelDoc.Close(); excelDoc = null; } } } else { ShowWarning(Common.GetString("SampleAddEditView", "NoRecordToBeExported")); } } } } } } |
Download project and refer to the "CustomScreenSampleForMC" file.