| public SetSheetHidden ( int sheetIx, SheetState state ) : void | ||
| sheetIx | int | |
| state | SheetState | |
| Résultat | void | |
public static byte[] GenerateTemplate(List<Business.Entities.company> listCompany,List<Business.Entities.contractor> listContractor,List<Business.Entities.project> listProject)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
int parseRecordNumber = 100; // number of rows that has style or validation
int startRowIndex = 3;
XSSFCellStyle styleCurrency;
XSSFCellStyle styleDate;
XSSFCellStyle styleNumeric;
XSSFCellStyle styleDecimal;
//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;
List<string> listCompanyString = new List<string>();
foreach(var data in listCompany)
{
listCompanyString.Add(data.name);
}
List<string> listContractorString = new List<string>();
foreach(var data in listContractor)
{
listContractorString.Add(data.name);
}
List<string> listProjectString = new List<string>();
foreach(var data in listProject)
{
listProjectString.Add(data.name);
}
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");
styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
List<string> columnList = new List<string>();
columnList.Add("Name");
int ContractorStringLocation = 1;
columnList.Add("Contractor");
columnList.Add("Photo");
columnList.Add("Description");
columnList.Add("Start Date");
columnList.Add("Finish Date");
columnList.Add("Highlight");
columnList.Add("Project Stage");
columnList.Add("Status");
columnList.Add("Budget");
columnList.Add("Currency");
columnList.Add("Num");
int PmcStringLocation = 12;
columnList.Add("Pmc");
columnList.Add("Summary");
int CompanyStringLocation = 14;
columnList.Add("Company");
columnList.Add("Status Non Technical");
columnList.Add("Is Completed");
columnList.Add("Completed Date");
int ProjectStringLocation = 18;
columnList.Add("Project");
columnList.Add("Submit For Approval Time");
columnList.Add("Approval Status");
columnList.Add("Approval Time");
columnList.Add("Deleted");
columnList.Add("Approval Message");
columnList.Add("Status Technical");
columnList.Add("Scurve Data");
sheet = (XSSFSheet)workbook.CreateSheet("Data");
int col = 0;
int rowNumber = 0;
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
dvHelper = new XSSFDataValidationHelper(sheet);
//header data
style = (XSSFCellStyle)workbook.CreateCellStyle();
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue("M Project");
font = (XSSFFont)workbook.CreateFont();
font.FontHeight = 24;
style.SetFont(font);
cell.CellStyle = style;
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//header data
foreach (string data in columnList)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = style;
//cell.CellStyle.IsLocked = true;
//column width
sheet.SetColumnWidth(col, (30 * 256));
++col;
}
//sheet.CreateFreezePane(0, 4);
//dropdownlist Company
if(listCompanyString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany");
int i=0;
foreach(string a in listCompanyString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,CompanyStringLocation,CompanyStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(1, true);
sheet.AddValidationData(validation);
}
//dropdownlist Contractor
if(listContractorString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor");
int i=0;
foreach(string a in listContractorString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ContractorStringLocation,ContractorStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(2, true);
sheet.AddValidationData(validation);
}
//dropdownlist Project
if(listProjectString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject");
int i=0;
foreach(string a in listProjectString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ProjectStringLocation,ProjectStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(3, true);
sheet.AddValidationData(validation);
}
/*Cell formatting*/
for (int i = startRowIndex; i <= parseRecordNumber; i++)
{
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//start_date
col = 4;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//finish_date
col = 5;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//budget
col = 9;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDecimal;
if(i==startRowIndex)
{
}
//num
col = 11;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleNumeric;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11);
dvHelper = new XSSFDataValidationHelper(sheet);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);
}
//completed_date
col = 17;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//submit_for_approval_time
col = 19;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//approval_time
col = 21;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
}
//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return ms.ToArray();
}