public static string WriteExcelFile4(IEnumerable <Company> companies) { Console.WriteLine("Running WriteExcelFile4"); /*This example shows writing a Company class to a file with a column mapping that writes the columns in order as they appear to the sheet */ //, formatCode: "#,##0.00" //, cellType: ExcelCellType.Currency, formatCode: "[Blue]$#,##0.00; [Red]-$#,##0.00;" //, cellType: ExcelCellType.Percent, formatCode: "0.00%" var map = new ColumnInfoList <Company>(); //the column type is inferred from the object type map.Add("Company Id", (c) => c.CompanyId, cellType: ExcelCellType.Number); map.Add("Legal Name", (c) => c.LegalName); //if updateheader is true we will update the header value. In this case WE have to pass the column name map.Add("E", "Doing Business As", (c) => c.DBAName, updateHeader: true); //specialiazed format codes can be used to format the data as desired. // NOTE: IF you are writing to an existing template, that already has the column formatted then this formatcode will be ignored map.Add("Change Date", (c) => c.ChangeDate, cellType: ExcelCellType.Date, formatCode: "mm-dd-yyyy"); map.Add("UserId", (c) => c.UserId); var path = Common.GetFileName(Common.XLS_TYPE); using (var writer = new ExcelWriter(Common.TemplatePath, 2, 3)) { writer.CreateSheetIfNotFound = true; writer.WriteDataToSheet("Companies", companies, map); writer.WriteTo(path); } return(path); }
public static string WriteExcelFile2(IEnumerable <Company> companies) { //companies = companies.Take(5000); Console.WriteLine("Running WriteExcelFile2"); /*This example shows writing a Company class a sheet using a simple map. */ var map = new ColumnInfoList <Company> { { "A", "Company Id", obj => obj.CompanyId }, { "B", "Legal Name", obj => obj.LegalName }, { "D", "DBA Name", obj => obj.DBAName }, { "E", "Change Date", obj => obj.ChangeDate } }; var path = Common.GetFileName(Common.XLS_TYPE); using (var writer = new ExcelWriter(1, 2)) { writer.WriteDataToSheet("Companies", companies, map); //showing appending to a sheet writer.WriteDataToSheet("Companies", companies, map, true); writer.WriteTo(path); } return(path); }
public void WriteFileWithNoTemplate() { int id = 1; var employees = Employee.GetTestEmployees(_employeesCount).ToList(); ColumnInfoList <Employee> employeesMap = new ColumnInfoList <Employee>(); //IList<IColumnInfo<Employee>> employeesMap = new List<IColumnInfo<Employee>>(); employeesMap.Add("A", "Id", (obj) => IncrementId(ref id), cellType: ExcelCellType.Number); employeesMap.Add("B", "Name", (obj) => obj.L1Name); employeesMap.Add("C", "SSN", (obj) => obj.L2SSN); ColumnInfoList <Employer> employerMap = new ColumnInfoList <Employer>(); //IList<IColumnInfo<Employer>> employerMap = new List<IColumnInfo<Employer>>(); employerMap.Add("A", "Name", (obj) => obj.L7Name); employerMap.Add("B", "City", (obj) => obj.L11City); employerMap.Add("C", "State", (obj) => obj.L12State); employerMap.Add("D", "Zip", (obj) => obj.L13Zip, cellType: ExcelCellType.Number); using (ExcelWriter writer = new ExcelWriter(1, 2)) { //completely control sheet creation by turning off header generation and passing in custom column infos writer.GenerateHeadersFromType = false; writer.WriteDataToSheet("Employees", employees, employeesMap); writer.WriteDataToSheet("Employer", Employer.GetEmployer(), employerMap); writer.GenerateHeadersFromType = true; writer.WriteDataToSheet("Employees No Maps", employees); writer.WriteTo(_destinationPath); } Assert.IsTrue(File.Exists(_destinationPath)); }
public void WriteFileTestUsingHeaderWithMaps() { var employees = Employee.GetTestEmployees(_employeesCount).ToList(); ColumnInfoList <Employee> employeesMap = new ColumnInfoList <Employee>(); //IList<IColumnInfo<Employee>> employeesMap = new List<IColumnInfo<Employee>>(); employeesMap.Add("A", "", (obj) => String.Empty, updateHeader: true); //example on how to remove a columns data, does not actually remove the column employeesMap.Add("L14All12Months", (obj) => obj.Blah1); employeesMap.Add("L15All12Months", (obj) => obj.Blah); employeesMap.Add("L16All12Months", (obj) => obj.Blah2); employeesMap.Add("AT", "Name", (obj) => String.Format("{0} {1}", obj.L1Name, obj.L2SSN)); //add a new column outside of the headers at column AT employeesMap.Add("AU", "Name 2", (obj) => ComplicatedStringMethodExample(obj)); //next two are just show different ways to call the lambda employeesMap.Add("AV", "Name 3", (obj) => ConcatName(obj)); employeesMap.Add(new ColumnInfo <Employee>("AW", "Hire Date", (obj) => obj.HireDate) { CellType = ExcelCellType.Date, FormatCode = "mm-dd-yyyy" }); //new way using (ExcelWriter writer = new ExcelWriter(_sourcePath, 1, 2)) { writer.WriteDataToSheet("Employees", employees, employeesMap); writer.WriteDataToSheet("Employer", Employer.GetEmployer()); writer.WriteTo(_destinationPath); } Assert.IsTrue(File.Exists(_destinationPath)); }
public static string WriteExcelFile5() { Console.WriteLine("Running WriteExcelFile5"); /*This example shows writing a Company class to a file with a column mapping that provides total control over the file layout and uses a iDataReader for the data source */ Func <int, string> col = (i) => FileIOUtilities.ConvertExcelNumberToLetter(i); var y = 1; //, formatCode: "#,##0.00" //, cellType: ExcelCellType.Currency, formatCode: "[Blue]$#,##0.00; [Red]-$#,##0.00;" //, cellType: ExcelCellType.Percent, formatCode: "0.00%" var map = new ColumnInfoList <Company> { { col(y++), "Company Id", (c) => c.CompanyId }, //insert a blank column at B for Foo, next column starts at C { col(y++), "Foo", (c) => null }, //, formatCode: "000-00-0000" { col(y++), "Legal Name", (c) => c.LegalName } }; map.Add(col(y++), "Doing Business As", (c) => c.DBAName, updateHeader: true); map.Add(col(y++), "Change Date", (c) => c.ChangeDate, cellType: ExcelCellType.Date, formatCode: "mm-dd-yyyy"); map.Add(col(y++), "UserId", (c) => c.UserId); var path = Common.GetFileName(Common.XLS_TYPE); using (var writer = new ExcelWriter(1, 3)) { writer.CreateSheetIfNotFound = true; using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DataModel"].ConnectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "Select top (10000) * from Company"; cmd.CommandType = CommandType.Text; using (var reader = cmd.ExecuteReader()) { writer.WriteDataToSheet("Companies", reader, map); writer.WriteTo(path); } } } } return(path); }
public TextFileTranslateParams(string txtPath, bool useHeaders, bool useQuotes, string extension, string dateFormat, string fieldDelim, int fieldWidth, NumberFormatInfo nfi, ColumnInfoList ciList, string culture, CallbackHandler cbh, string dummyTime) { TxtPath = txtPath; UseHeaders = useHeaders; UseQuotes = useQuotes; Extension = extension; DateFormat = dateFormat; FieldDelim = fieldDelim; FieldWidth = fieldWidth; DummyTime = dummyTime; NFI = nfi; ColInfoList = ciList; this.cbh = cbh; }
public void WriteFileTestNoHeaderCustomMappings() { var employees = Employee.GetTestEmployees(_employeesCount).ToList(); ColumnInfoList <Employee> employeesMap = new ColumnInfoList <Employee>(); //add our own weird custom mappings employeesMap.Add("A", "Name", (emp) => emp.L1Name); employeesMap.Add("C", "SSN", (emp) => emp.L2SSN.Replace("-", ""), cellType: ExcelCellType.Number, formatCode: "000-00-0000"); employeesMap.Add("E", "Blah Col Set 1", (emp) => emp.Blah); employeesMap.Add("G", "Blah1 Col Set 1", (emp) => emp.Blah1); employeesMap.Add("I", "Blah2 Col Set 1", (emp) => emp.Blah2); employeesMap.Add("K", "L15 Jan Set 1", (emp) => emp.L15Jan, formatCode: "#,##0.00"); employeesMap.Add("M", "L15 Jun Set 1", (emp) => Convert.ToDouble(emp.L15June) / 100d, cellType: ExcelCellType.Percent); employeesMap.Add("O", "Hire Date", (emp) => emp.HireDate, formatCode: "mm-dd-yyyy"); employeesMap.Add("Q", "Hourly Rate", (emp) => GetRate(emp.HourlyRate), cellType: ExcelCellType.Currency, formatCode: "[Blue]$#,##0.00; [Red]-$#,##0.00;"); employeesMap.Add("AA", "Col 1", (emp) => emp.L1Name); employeesMap.Add("AC", "Col 2", (emp) => emp.L2SSN); employeesMap.Add("AE", "Col 3", (emp) => emp.Blah); employeesMap.Add("AG", "Col 4", (emp) => emp.Blah1); employeesMap.Add("AI", "Col 5", (emp) => emp.Blah2); employeesMap.Add("AK", "Col 6", (emp) => PadLeft("000000", emp.L15Jan.ToString()), cellType: ExcelCellType.Quoted); employeesMap.Add("AM", "Col 7", (emp) => Convert.ToDouble(emp.L15June) / 100d, cellType: ExcelCellType.Percent, formatCode: "0.00%"); employeesMap.Add("AO", "Col 8", (emp) => emp.HireDate); employeesMap.Add("AQ", "Col 9", (emp) => GetRate(emp.HourlyRate), cellType: ExcelCellType.Currency); ColumnInfoList <Employer> employerMap = new ColumnInfoList <Employer>(); employerMap.Add(null, "L13Zip", (emp) => emp.L13Zip, cellType: ExcelCellType.Number); //setting a number less than 1 for the header row will cause the writer to generate the headers from the properties, so to completely jack with the format, lets override them all using (ExcelWriter writer = new ExcelWriter(1, 2)) { writer.CreateSheetIfNotFound = true; writer.GenerateHeadersFromType = false; //we are going to lay out the sheet manually using the map writer.WriteDataToSheet("Employees 2", employees, employeesMap); writer.GenerateHeadersFromType = true; //turn this back on for this sheet writer.WriteDataToSheet("Employer", Employer.GetEmployer(), employerMap); writer.WriteTo(_destinationPath); } Assert.IsTrue(File.Exists(_destinationPath)); }