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));
}
