/// <summary>
/// 生成excel
/// </summary>
/// <param name="templatePath">模板路径</param>
/// <param name="saveFolder">保存文件夹路径</param>
/// <param name="sheetName">工作薄名</param>
/// <param name="ds">数据源</param>
public static string ExecuteExcelExport(string templatePath, string saveFolder, string sheetName, DataSet ds)
{
try
{
HSSFWorkbook hssfworkbook = InitializeWorkbook(templatePath);
if (hssfworkbook == null)
{
return(null);
}
ISheet sheet1 = hssfworkbook.GetSheet(sheetName);
//删除其他工作薄
int sheetIndex = hssfworkbook.GetSheetIndex(sheet1);
if (hssfworkbook.NumberOfSheets > 1)
{
for (int i = hssfworkbook.NumberOfSheets - 1; i >= 0; i--)
{
if (i != sheetIndex)
{
hssfworkbook.RemoveSheetAt(i);
}
}
}
hssfworkbook.FirstVisibleTab = 0;
//相关参数
int columnHeaderIndex = GetGridRowIndex(sheet1, "行号");
if (columnHeaderIndex <= 0)
{
columnHeaderIndex = GetGridRowIndex(sheet1, "序号");
}
int gridRowCount = ds.Tables["Detail"].Rows.Count;
DataTable dtEnterprise = ds.Tables["Enterprise"];
DataTable dtMain = ds.Tables["Main"];
DataTable dtDetail = ds.Tables["Detail"];
//重新构造工作薄
CreateSheet(sheet1, columnHeaderIndex + 2, gridRowCount);
decimal totalMoney = 0; //总金额
decimal totalVolume = 0; //总体积
int totalBoxAmount = 0; //总箱数
int totalAmount = 0; //总数量
//企业信息
SetCellValueByTemplateStr(sheet1, "{$EnterpriseName}", dtEnterprise.Rows[0]["EnterpriseName"]);
SetCellValueByTemplateStr(sheet1, "{$EnterprisePhone}", dtEnterprise.Rows[0]["Telephone"]);
SetCellValueByTemplateStr(sheet1, "{$EnterpriseFax}", dtEnterprise.Rows[0]["Fax"]);
SetCellValueByTemplateStr(sheet1, "{$EnterpriseMobile}", dtEnterprise.Rows[0]["Mobile"]);
//数据
for (int i = 0; i < dtMain.Columns.Count; i++)
{
SetCellValueByTemplateStr(sheet1, "{$" + dtMain.Columns[i].ColumnName + "}", dtMain.Rows[0][i]);
}
//表明细
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
for (int j = 0; j < dtDetail.Columns.Count; j++)
{
SetCellValueByTemplateStr(sheet1, "{$" + dtDetail.Columns[j].ColumnName + "}", dtDetail.Rows[i][j], columnHeaderIndex + 1, gridRowCount, true);
}
totalMoney += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["Money"]);
totalBoxAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["BoxAmount"]);
totalAmount += DataTypeConvert.ToInt32(dtDetail.Rows[i]["Amount"]);
totalVolume += DataTypeConvert.ToDecimal(dtDetail.Rows[i]["OutVolumeDisplay"]);
}
//合计
SetCellValueByTemplateStr(sheet1, "{$TotalMoneyCH}", ConvertChIAmountHelper.ConvertSum(totalMoney + "")); //大写
SetCellValueByTemplateStr(sheet1, "{$TotalBoxAmount}", "共:" + totalBoxAmount + "箱"); //总箱数
SetCellValueByTemplateStr(sheet1, "{$TotalAmount}", totalAmount); //总数量
SetCellValueByTemplateStr(sheet1, "{$TotalMoney}", totalMoney); //总金额
SetCellValueByTemplateStr(sheet1, "{$TotalVolume}", totalVolume.ToString("f2")); //总体积
//生成文件
if (!System.IO.Directory.Exists(saveFolder))
{
System.IO.Directory.CreateDirectory(saveFolder);
}
string savePath = saveFolder + dtMain.Rows[0]["OrderCode"] + ".xls";
using (FileStream file = new FileStream(savePath, FileMode.Create))
{
hssfworkbook.Write(file);
file.Close();
}
return(dtMain.Rows[0]["OrderCode"] + ".xls");
}
catch (Exception e)
{
}
return("");
}
public void ActiveSheetAfterDelete_bug40414()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0");
Npoi.Core.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
Npoi.Core.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
Npoi.Core.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
Npoi.Core.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
// Confirm default activation/selection
ConfirmActiveSelected(sheet0, true);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetActiveSheet(3);
wb.SetSelectedTab(3);
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, true);
ConfirmActiveSelected(sheet4, false);
wb.RemoveSheetAt(3);
// after removing the only active/selected sheet, another should be active/selected in its place
if (!sheet4.IsSelected)
{
throw new AssertionException("identified bug 40414 a");
}
if (!sheet4.IsActive)
{
throw new AssertionException("identified bug 40414 b");
}
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet4, true);
sheet3 = sheet4; // re-align local vars in this Test case
// Some more cases of removing sheets
// Starting with a multiple selection, and different active sheet
wb.SetSelectedTabs(new int[] { 1, 3, });
wb.SetActiveSheet(2);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
ConfirmActiveSelected(sheet3, false, true);
// removing a sheet that is not active, and not the only selected sheet
wb.RemoveSheetAt(3);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
// removing the only selected sheet
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet2, true, true);
// The last remaining sheet should always be active+selected
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, true, true);
}
public ActionResult ExportStats(Schools school, UserGroup ugroup, int year)
{
var stats = new ClassStatistics {
school = school, usergroup = ugroup, year = year
};
stats.CalculateStats(repository);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
var colcount = 0;
// show general stats first
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Malaysian"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Foreigners"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("SubTotal"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < 3; i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
row = sheet.CreateRow(rowcount++);
row.CreateCell(0, CellType.NUMERIC).SetCellValue(stats.msian_male);
row.CreateCell(1, CellType.NUMERIC).SetCellValue(stats.msian_female);
row.CreateCell(2, CellType.NUMERIC).SetCellValue(stats.foreign_male);
row.CreateCell(3, CellType.NUMERIC).SetCellValue(stats.foreign_female);
row.CreateCell(4, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male);
row.CreateCell(5, CellType.NUMERIC).SetCellValue(stats.msian_female + stats.foreign_female);
row.CreateCell(6, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male + stats.msian_female + stats.foreign_female);
foreach (var entry in stats.collections)
{
// class row
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(SecurityElement.Escape(entry.name));
row.GetCell(0).CellStyle = boldStyle;
// header row1
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape(race.name));
colcount += 2;
}
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
// header row2
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < entry.GetList().Count(); i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
// stats row
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount++).SetCellValue(race.male);
row.CreateCell(colcount++).SetCellValue(race.female);
}
row.CreateCell(colcount).SetCellValue(entry.GetList().Sum(x => x.male + x.female));
}
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Statistics_{0}_{1}.xls", ugroup, school)));
}
public ActionResult Export(bool dob, bool name, bool gender, bool guardian, bool sclass, bool ugroup, bool citizenship,
bool contactnos, bool occupation, bool school, bool birthcert, bool icpassport, bool address, bool children, bool race, bool rank)
{
var searchcookie = Request.Cookies["search"];
IEnumerable <user> results = Enumerable.Empty <user>();
var search = new UserSearch();
if (searchcookie != null)
{
var serializer = new JavaScriptSerializer();
search = serializer.Deserialize <UserSearch>(HttpUtility.UrlDecode(searchcookie.Value));
results = repository.GetUsers(sessionid.Value, auth, search.school, search.sclass, search.group, search.discipline, search.attStatus, search.date, search.year, search.seca, search.status ?? true);
if (!string.IsNullOrEmpty(search.term))
{
long userid;
if (long.TryParse(search.term, out userid))
{
results = repository.GetUsers().Where(x => x.id == userid);
}
else
{
var lsearch = new LuceneSearch();
var ids = lsearch.UserSearch(search.term.ToLowerInvariant());
results = results.Where(x => ids.Select(y => y.id).Contains(x.id.ToString())).AsEnumerable();
results = results.Join(ids, x => x.id.ToString(), y => y.id, (x, y) => new { x, y.score })
.OrderByDescending(x => x.score).Select(x => x.x);
}
}
}
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var rowcount = 0;
var sheet = templateWorkbook.CreateSheet("Sheet1");
var row = sheet.CreateRow(rowcount++);
// bold font
var boldStyle = templateWorkbook.CreateCellStyle();
var fontBold = templateWorkbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(fontBold);
var colcount = 0;
var totalcolumns = 0;
// initialise heading
row.CreateCell(colcount++).SetCellValue("No");
if (name)
{
row.CreateCell(colcount++).SetCellValue("Name");
}
if (ugroup)
{
row.CreateCell(colcount++).SetCellValue("UserGroup");
}
if (race)
{
row.CreateCell(colcount++).SetCellValue("Race");
}
if (gender)
{
row.CreateCell(colcount++).SetCellValue("Gender");
}
if (dob)
{
row.CreateCell(colcount++).SetCellValue("DOB");
}
if (school)
{
row.CreateCell(colcount++).SetCellValue("School");
}
if (sclass)
{
row.CreateCell(colcount++).SetCellValue("Class");
}
if (citizenship)
{
row.CreateCell(colcount++).SetCellValue("Citizenship");
}
if (occupation)
{
row.CreateCell(colcount++).SetCellValue("Father Occupation");
row.CreateCell(colcount++).SetCellValue("Mother Occupation");
row.CreateCell(colcount++).SetCellValue("Guardian Occupation");
}
if (contactnos)
{
row.CreateCell(colcount++).SetCellValue("Contact Info");
}
if (birthcert)
{
row.CreateCell(colcount++).SetCellValue("Birth Cert");
}
if (icpassport)
{
row.CreateCell(colcount++).SetCellValue("NRIC");
row.CreateCell(colcount++).SetCellValue("Passport");
}
if (address)
{
row.CreateCell(colcount++).SetCellValue("Address");
}
if (guardian)
{
row.CreateCell(colcount++).SetCellValue("Father");
row.CreateCell(colcount++).SetCellValue("Father Contact");
row.CreateCell(colcount++).SetCellValue("Mother");
row.CreateCell(colcount++).SetCellValue("Mother Contact");
row.CreateCell(colcount++).SetCellValue("Guardian");
row.CreateCell(colcount++).SetCellValue("Guardian Contact");
}
if (children)
{
row.CreateCell(colcount++).SetCellValue("Children Details");
}
if (rank)
{
row.CreateCell(colcount++).SetCellValue("Rank");
}
totalcolumns = colcount;
// now intialise data
foreach (var usr in results.OrderBy(x => x.name))
{
colcount = 0;
row = sheet.CreateRow(rowcount);
row.CreateCell(colcount++).SetCellValue(rowcount);
if (name)
{
row.CreateCell(colcount++).SetCellValue(SecurityElement.Escape(usr.ToName(false)));
}
if (ugroup)
{
row.CreateCell(colcount++).SetCellValue(((UserGroup)usr.usergroup).ToString());
}
if (race)
{
var usr_race = "";
if (!string.IsNullOrEmpty(usr.race))
{
usr_race = usr.race;
}
row.CreateCell(colcount++).SetCellValue(usr_race);
}
if (gender)
{
var usr_gender = "";
if (!string.IsNullOrEmpty(usr.gender))
{
usr_gender = usr.gender.Substring(0, 1);
}
row.CreateCell(colcount++).SetCellValue(usr_gender);
}
if (dob)
{
var usr_dob = "";
if (usr.dob.HasValue)
{
usr_dob = usr.dob.Value.ToShortDateString();
}
row.CreateCell(colcount++).SetCellValue(usr_dob);
}
if (school)
{
var usr_school = "";
if (search.school.HasValue)
{
usr_school = ((Schools)search.school.Value).ToString();
}
row.CreateCell(colcount++).SetCellValue(usr_school);
}
if (sclass)
{
var class_name = "";
if (usr.usergroup == (int)UserGroup.STUDENT)
{
var allocated = usr.classes_students_allocateds.SingleOrDefault(x => x.year == search.year);
if (allocated != null)
{
class_name = allocated.school_class.name;
}
}
else if (usr.usergroup == (int)UserGroup.TEACHER)
{
var allocated =
usr.classes_teachers_allocateds.Where(x => x.year == search.year);
if (allocated.Count() != 0)
{
class_name = string.Join(",", allocated.Select(x => x.school_class.name).ToArray());
}
}
row.CreateCell(colcount++).SetCellValue(SecurityElement.Escape(class_name));
}
if (citizenship)
{
var cship = "";
if (!string.IsNullOrEmpty(usr.citizenship))
{
cship = usr.citizenship;
}
row.CreateCell(colcount++).SetCellValue(cship);
}
if (occupation)
{
// father
var father_occ =
usr.students_guardians.SingleOrDefault(x => x.type == GuardianType.FATHER.ToInt());
if (father_occ != null)
{
row.CreateCell(colcount++).SetCellValue(father_occ.user1.user_parents.occupation);
}
else
{
row.CreateCell(colcount++).SetCellValue("-");
}
// mother
var mother_occ =
usr.students_guardians.SingleOrDefault(x => x.type == GuardianType.MOTHER.ToInt());
if (mother_occ != null)
{
row.CreateCell(colcount++).SetCellValue(mother_occ.user1.user_parents.occupation);
}
else
{
row.CreateCell(colcount++).SetCellValue("-");
}
// guardian
var guardian_occ =
usr.students_guardians.SingleOrDefault(x => x.type == GuardianType.GUARDIAN.ToInt());
if (guardian_occ != null)
{
row.CreateCell(colcount++).SetCellValue(guardian_occ.user1.user_parents.occupation);
}
else
{
row.CreateCell(colcount++).SetCellValue("-");
}
}
if (contactnos)
{
row.CreateCell(colcount++).SetCellValue(SecurityElement.Escape(usr.ToContactString()));
}
if (birthcert)
{
var usr_birthcert = "";
if (!string.IsNullOrEmpty(usr.birthcertno))
{
usr_birthcert = usr.birthcertno;
}
row.CreateCell(colcount++).SetCellValue(usr_birthcert);
}
if (icpassport)
{
var usr_passport = "";
var usr_ic = "";
if (!string.IsNullOrEmpty(usr.nric_new))
{
usr_ic = usr.nric_new;
}
if (!string.IsNullOrEmpty(usr.passportno))
{
usr_passport = usr.passportno;
}
row.CreateCell(colcount++).SetCellValue(usr_ic);
row.CreateCell(colcount++).SetCellValue(usr_passport);
}
if (address)
{
var usr_address = "";
if (!string.IsNullOrEmpty(usr.address))
{
usr_address = usr.address;
}
row.CreateCell(colcount++).SetCellValue(usr_address);
}
if (guardian)
{
if (usr.usergroup == (int)UserGroup.STUDENT)
{
var fathername = row.CreateCell(colcount++);
var fathercontact = row.CreateCell(colcount++);
var mothername = row.CreateCell(colcount++);
var monthercontact = row.CreateCell(colcount++);
var guardianname = row.CreateCell(colcount++);
var guardiancontact = row.CreateCell(colcount++);
var currentfather =
usr.students_guardians.Where(
x => x.type == (int)GuardianType.FATHER).Select(x => x.user1)
.FirstOrDefault();
var currentmother = usr.students_guardians.Where(
x => x.type == (int)GuardianType.MOTHER).Select(x => x.user1)
.FirstOrDefault();
var currentguardian = usr.students_guardians.Where(
x => x.type == (int)GuardianType.GUARDIAN).Select(x => x.user1)
.FirstOrDefault();
if (currentfather != null)
{
fathername.SetCellValue(currentfather.ToName(false));
fathercontact.SetCellValue(currentfather.ToContactString());
}
else
{
fathername.SetCellValue("-");
fathercontact.SetCellValue("-");
}
if (currentmother != null)
{
mothername.SetCellValue(currentmother.ToName(false));
monthercontact.SetCellValue(currentmother.ToContactString());
}
else
{
mothername.SetCellValue("-");
monthercontact.SetCellValue("-");
}
if (currentguardian != null)
{
guardianname.SetCellValue(currentguardian.ToName(false));
guardiancontact.SetCellValue(currentguardian.ToContactString());
}
else
{
guardianname.SetCellValue("-");
guardiancontact.SetCellValue("-");
}
}
}
if (children)
{
string childrenString = "";
if (usr.usergroup == (int)UserGroup.GUARDIAN)
{
var childrenlist =
usr.students_guardians1
.ToChildrenModel(search.year)
.Select(x => string.Format("{0} {1}", x.name, !string.IsNullOrEmpty(x.class_name)?string.Format("{0}", x.class_name):"")).
ToArray();
childrenString = string.Join(", ", childrenlist);
}
row.CreateCell(colcount++).SetCellValue(SecurityElement.Escape(childrenString));
}
if (rank)
{
string rankValue = "";
if (usr.usergroup == (int)UserGroup.STUDENT)
{
rankValue = usr.ToRankStudent();
}
row.CreateCell(colcount++).SetCellValue(rankValue);
}
rowcount++;
}
// do some formatting
row = sheet.GetRow(0);
for (int i = 0; i < totalcolumns; i++)
{
var cell = row.GetCell(i);
cell.CellStyle = boldStyle;
sheet.AutoSizeColumn(i);
}
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("UserExport_{0}.xls", DateTime.Now.ToShortDateString().Replace("/", ""))));
}
private void button1_Click(object sender, EventArgs e)
{
createDataTable();
var strFilePath = "template.xlt";
HSSFWorkbook workbook;
using (var fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))
{
workbook = new HSSFWorkbook(fs);
fs.Close();
}
if (workbook != null)
{
//load template
var RawData = (HSSFSheet)workbook.GetSheet("RawData");
//data to newSheet
var hst = RawData;
for (var i = 0; i < ProductInfo.Rows.Count; i++)
{
var hr = (HSSFRow)hst.CreateRow(i + 1);
for (var j = 0; j < ProductInfo.Columns.Count; j++)
{
var hc = (HSSFCell)hr.CreateCell(j);
//Notice!!! Qty is Int,other String.
if (ProductInfo.Columns[j].Caption == "_Qty")
{
hc.SetCellType(CellType.Numeric);
if (!string.IsNullOrEmpty(ProductInfo.Rows[i][j].ToString()))
{
var number = Convert.ToInt32(ProductInfo.Rows[i][j].ToString());
hc.SetCellValue(number);
}
else
{
hc.SetCellValue(ProductInfo.Rows[i][j].ToString());
}
}
else
{
hc.SetCellValue(ProductInfo.Rows[i][j].ToString());
}
}
}
//export new EXCEL file
var filename = "P_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls";
var fsExcelNew = new FileStream(filename, FileMode.Create);
workbook.Write(fsExcelNew);
//remove sheet
workbook.RemoveSheetAt(0);
workbook = null;
fsExcelNew.Close();
//Open excel
//System.Windows.Forms.Application.StartupPath + "\\" + filename;
var file = @"C:\Windows\explorer.exe";
Process.Start(file, filename);
}
}
/// <summary>
/// 根据模板将数据导出到Excel(多个工作簿)
/// </summary>
/// <param name="templatePath">excel模板路径(全路径)</param>
/// <param name="dicColumns">要导出的列集合(如果为空默认导出所有已配置的列,字典键：类别)</param>
/// <param name="dicDicData">要导出的数据集(字典键值：类别)</param>
/// <param name="sheetName">要导出的工作簿名称</param>
/// <param name="fileName">导出的文件名称(包括路径)</param>
/// <returns></returns>
public virtual bool ExportDataToExcelByTemplate(string templatePath, Dictionary <string, List <ExportColumn> > dicColumns, Dictionary <string, Dictionary <string, DataTable> > dicDicData, string sheetName, string fileName)
{
//创建excel模板对象
HSSFWorkbook workbook = LoadWorkbook(templatePath);
if (workbook == null)
{
return(false);
}
//获取模板工作簿
HSSFSheet sheetTemplate = (HSSFSheet)workbook.GetSheetAt(0);
foreach (KeyValuePair <string, Dictionary <string, DataTable> > itemSheet in dicDicData)
{
sheetTemplate.CopyTo(workbook, itemSheet.Key, true, true);//复制模板工作簿为新的工作簿
}
//变量
string cellTemplate = "";
object cellValue = "";
int lineCount = 0; //明细行总数
int lineHeaderRowIndex = 0; //明细行头行索引
int k = 0;
DataRow dr = null;
//多个工作簿
ISheet sheet = null;
Dictionary <string, DataTable> dicData = null;
foreach (KeyValuePair <string, Dictionary <string, DataTable> > itemSheet in dicDicData)
{
sheet = workbook.GetSheet(itemSheet.Key); //新的工作簿
dicData = itemSheet.Value; //工作簿数据字典
#region 替换模板值
cellTemplate = "";
cellValue = "";
lineCount = 0; //明细行总数
lineHeaderRowIndex = 0; //明细行头行索引
k = 0;
dr = null;
//替换模板的值
foreach (KeyValuePair <string, List <ExportColumn> > item in dicColumns)
{
#region 验证模板内容
//如果数据不存在,跳过
if (!dicData.ContainsKey(item.Key))
{
continue;
}
#endregion
#region 分类进行替换
//分类进行替换
if (item.Key.EndsWith("Line"))
{
#region 替换明细值
//变量
lineHeaderRowIndex = GetTemplateRowIndexByCellCommentString(sheet, item.Key);
if (lineHeaderRowIndex < 0)
{
lineHeaderRowIndex = GetTemplateRowIndexByCellValue(sheet, "行号");
}
if (lineHeaderRowIndex < 0)
{
continue;
}
lineCount = dicData[item.Key].Rows.Count;
//创建明细行模板
CreateLineTemplate(sheet, lineHeaderRowIndex + 2, lineCount);
//替换明细行的值
k = 0;
dr = null;
for (int i = lineHeaderRowIndex + 1; i < lineHeaderRowIndex + lineCount + 1; i++)
{
dr = dicData[item.Key].Rows[k];
//替换相应的值
foreach (ExportColumn col in dicColumns[item.Key])
{
cellTemplate = "{$" + col.ColumnName + "}";
cellValue = dr[col.ColumnName];
ReplaceCellValueByTemplateStr(sheet, cellTemplate, cellValue, col.DataType, i, i);
}
k++;
}
#endregion
}
else
{
#region 替换非明细值
//替换非明细值
foreach (ExportColumn col in item.Value)
{
cellTemplate = "{$" + col.ColumnName + "}";
cellValue = GetTemplateStrValue(dicData[item.Key], col.ColumnName);
ReplaceCellValueByTemplateStr(sheet, cellTemplate, cellValue, col.DataType, sheet.FirstRowNum, sheet.LastRowNum);
}//end foreach (ExportColumn col in item.Value)
#endregion
}
#endregion
}
#endregion
}
workbook.RemoveSheetAt(0);//删除模板工作簿
#region 生成Excel文件
using (FileStream file = new FileStream(fileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
#endregion
return(true);
}
public ActionResult ExportStats(string from, string to, Schools school)
{
var start = DateTime.Parse(from);
var end = DateTime.Parse(to);
var stats = new ConductStatistics();
stats.PopulateStats(start, end, school);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.IsBold = true;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue("Behaviour Type");
row.GetCell(0).CellStyle = boldStyle;
row.CreateCell(1).SetCellValue("Students");
row.GetCell(1).CellStyle = boldStyle;
row.CreateCell(2).SetCellValue("Incidents");
row.GetCell(2).CellStyle = boldStyle;
foreach (var stat in stats.demerits.OrderByDescending(x => x.totalIncidents))
{
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(stat.name);
row.CreateCell(1).SetCellValue(stat.totalStudents);
row.CreateCell(2).SetCellValue(stat.totalIncidents);
}
foreach (var stat in stats.merits.OrderByDescending(x => x.totalIncidents))
{
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(stat.name);
row.CreateCell(1).SetCellValue(stat.totalStudents);
row.CreateCell(2).SetCellValue(stat.totalIncidents);
}
// adjust column
sheet.AutoSizeColumn(0);
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Conduct_{0}_{1}.xls", from.Replace("/", ""), to.Replace("/", ""))));
}
public static MemoryStream ToMemoryStram(SummaryInfo summaryInfo,
List <Matrix <InspectionDataReport> > list_InspectionDataReportMatrix,
List <DefectTypeInfoReport> list_DefectTypeInfoReport,
DefectStatistics defectStatistics,
int ReportModel)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(ExcelText.SheetName_SummaryOperator);
#region SummaryOperator
int rowIndex = 0;
int code2drowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
//5、开始日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartDate, summaryInfo.StartDate);
//6、开始时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartTime, summaryInfo.StartTime);
//7、结束日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndDate, summaryInfo.EndDate);
//8、结束时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndTime, summaryInfo.EndTime);
rowIndex++;
//10、批次理论盘数 没有输入默认10 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.TotalNumberOfStrips, summaryInfo.TotalNumberOfStrips.ToString());
//11、已检测的盘数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsInspected, summaryInfo.NumberOfStripsInspected.ToString());
//12、未检测的盘数 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsNotInspected, summaryInfo.NumberOfStripsNotInspected.ToString());
//13、单盘芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.QuantityOfDevicesPerStrip, summaryInfo.QuantityOfDevicesPerStrip.ToString());
//14、批次理论芯片数
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStartQuantity, summaryInfo.NumberOfStartQuantity.ToString());
//15、批次已检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesInspected, summaryInfo.NumberOfDevicesInspected.ToString());
//16、每小时检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DevicesPerHour, string.Format("{0:0.##}", summaryInfo.DevicesPerHour));
rowIndex++;
//17、合格芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesPassed, summaryInfo.NumberOfDevicesPassed.ToString());
//18、不合格的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesRejected, summaryInfo.NumberOfDevicesRejected.ToString());
//19、误检芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesFalseCalled, summaryInfo.NumberOfDevicesFalseCalled.ToString());
//新增 OK复看为NG数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "OK复看NG数", summaryInfo.NumberOfReviewNG.ToString());
//20、跳过的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfNoDies, summaryInfo.NumberOfNoDies.ToString());
//21、合格率 存在二维码时根据二维码总数
if (ReportModel == 1)
{
//二维码总数 只在武汉二维码报表模式生成
CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}", "二维码总数"), summaryInfo.CodeNumber.ToString());
double temp = summaryInfo.NumberOfDevicesPassed / (double)summaryInfo.CodeNumber;
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", temp.ToString()));
}
else
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", summaryInfo.YieldByDevice));
}
//22、误检率
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.FalseCallDevicePercent, string.Format("{0:0.##}", summaryInfo.FalseCallDevicePercent));
//新增 当存在 K2N时显示算上复看OK到KG的误检率
if (summaryInfo.NumberOfReviewNG != 0)
{
// CreateOneRowTwoColumnCells(sheet, ref rowIndex, "误检率（%包含复看不合格）", string.Format("{0:0.##}", summaryInfo.DevicePercentOfK2N));
}
rowIndex++;
//23、错误种类出现数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType, ExcelText.Count);
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
if (defectTypeInfoReport.Count > 0)
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", defectTypeInfoReport.Index, defectTypeInfoReport.DefectType),
defectTypeInfoReport.Count.ToString());
}
}
rowIndex++;
//24、错误种类根据错误优先级统计数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType + "(根据错误优先级表)", ExcelText.Count);
if (defectStatistics.Flag == false)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告，存在未规定的错误优先级", "");
}
else
{
foreach (KeyValuePair <int, int> ds in defectStatistics.CountDefectResult)
{
if (defectStatistics.CountN2KDefectResult.ContainsKey(ds.Key))
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
string.Format("{0}-{1}={2}", ds.Value, defectStatistics.CountN2KDefectResult[ds.Key], ds.Value - defectStatistics.CountN2KDefectResult[ds.Key]));
}
else
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
ds.Value.ToString());
}
}
//25、是否存在优先级相同并且一同输出的
if (defectStatistics.List_RepeatPriority.Count != 0)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告 存在优先级相同情况", "");
for (int i = 0; i < defectStatistics.List_RepeatPriority.Count; i++)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectStatistics.List_RepeatPriority[i].ToString(), "");
}
}
}
sheet.SetColumnWidth(0, 80 * 256);
sheet.SetColumnWidth(0, 30 * 256);
#endregion
workbook.CreateSheet(ExcelText.SheetName_MapOperator);
workbook.CreateSheet(ExcelText.SheetName_UDD);
int T = 0;
#region 创建错误图片Sheet
sheet = workbook.CreateSheet("错误图片");
int sheetIndex = workbook.GetSheetIndex(sheet);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
int ImageSheetNum = 0;
T = T + rowIndex;
rowIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (rowIndex >= 65000)
{
ImageSheetNum++;
sheet = workbook.CreateSheet("错误图片续_" + ImageSheetNum);
sheetIndex = workbook.GetSheetIndex(sheet);
rowIndex = 0;
}
HSSFPatriarch sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFCell cell = null;
int defectCountInFrame = 0;
//int sheetContinuedIndexEachStrip = 0;
int ImageCol = 0;
foreach (InspectionDataReport dataReport in inspectionDataReportMatrix)
{
defectCountInFrame++;
int TempImage_row = 0;
ImageCol = 5;
int startRow = 0;
if (dataReport == null)
{
continue;
}
if (dataReport.List_DefectData.Count == 0)
{
if (dataReport.InspectionResult != InspectionResult.K2N)
{
continue;
}
}
startRow = rowIndex;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("第{0}条", dataReport.FrameIndex));
if (dataReport.Code2D != "null")
{
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(2);
cell.SetCellValue(string.Format("二维码：{0}", dataReport.Code2D));
}
// 创建图片 所有缺陷在一张图中 如果存在拍多次的情况 进行横排 第一张图 col为5
System.Drawing.Image[] images = new System.Drawing.Image[dataReport.List_GeneralImageTempPath.Count];
for (int i = 0; i < images.Count(); i++)
{
//创建图片单元格
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(ImageCol);
if (File.Exists(dataReport.List_GeneralImageTempPath[i]))
{
byte[] bytes = File.ReadAllBytes(dataReport.List_GeneralImageTempPath[i]);
int index = workbook.AddPicture(bytes, PictureType.JPEG);
System.Drawing.Image image;
using (MemoryStream ms = new MemoryStream(bytes))
{
image = System.Drawing.Image.FromStream(ms);
}
double imageWidth = image.Width;
double imageHeight = image.Height;
image.Dispose();
double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
int imageInCellColumns = (int)(imageWidth / cellWidth);
int imageInCellRows = (int)(imageHeight / cellHeight);
double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, ImageCol, rowIndex, imageInCellColumns + ImageCol, rowIndex + imageInCellRows);
sheetPatriarch.CreatePicture(anchor, index);
//计算图片高占多少个单元格
TempImage_row = (int)Math.Ceiling(imageHeight / cellHeight) + 1;
NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex + TempImage_row, 1);
dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
}
else
{
cell.SetCellValue("图片不存在");
}
ImageCol = ImageCol + 10;
}
rowIndex = rowIndex + 1;
//记录错误信息
foreach (var defectReort in dataReport.List_DefectData)
{
rowIndex = rowIndex + 1;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
if (defectReort.DefectTypeIndex <= 0 || defectReort.DefectTypeIndex > list_DefectTypeInfoReport.Count)
{
cell.SetCellValue(string.Format("{0}:{1}", defectReort.DefectTypeIndex, defectReort.ErrorDetail));
}
else
{
cell.SetCellValue(string.Format("{0}:{1}", list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType, defectReort.ErrorDetail));
}
}
rowIndex = rowIndex + 1;
string[] show_defecttype = new string[dataReport.List_DefectData.Count];
for (int i = 0; i < dataReport.List_DefectData.Count; i++)
{
show_defecttype[i] = dataReport.List_DefectData[i].DefectTypeIndex.ToString();
}
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("详细错误码：{0}", string.Join(";", show_defecttype)));
//判断 图片所占行数多 还是写入的错误信息行数多
if (rowIndex - startRow <= TempImage_row)
{
rowIndex = startRow + TempImage_row + 2;
}
#region 每个缺陷对应一张图 已屏蔽
//foreach (var defectReort in dataReport.List_DefectData)
//{
// if (rowIndex>= 32757)
// {
// sheetContinuedIndexEachStrip++;
// sheet = workbook.CreateSheet(inspectionDataReportMatrix.FrameName + " " + ExcelText.Continued + sheetContinuedIndexEachStrip.ToString());
// sheetIndex = workbook.GetSheetIndex(sheet);
// sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// rowIndex = 0;
// }
// defectCountInFrame++;
// sheet.CreateRow(rowIndex);
// sheet.CreateRow(rowIndex + 1);
// HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
// cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// cell.SetCellValue(list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType);
// cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(1);
// cell.SetCellValue(defectReort.ErrorDetail);
// rowIndex += 2;
// sheet.CreateRow(rowIndex);
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// if (File.Exists(defectReort.ImageTempPath))
// {
// byte[] bytes = File.ReadAllBytes(defectReort.ImageTempPath);
// int index = workbook.AddPicture(bytes, PictureType.JPEG);
// System.Drawing.Image image;
// using (MemoryStream ms = new MemoryStream(bytes))
// {
// image = System.Drawing.Image.FromStream(ms);
// }
// double imageWidth = image.Width;
// double imageHeight = image.Height;
// image.Dispose();
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageWidth / cellWidth);
// int imageInCellRows = (int)(imageHeight / cellHeight);
// double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
//
// //HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// //commentAnchor.AnchorType = AnchorType.MoveDontResize;
// //HSSFComment comment = (HSSFComment)sheetPatriarch.CreateCellComment(commentAnchor);
// //comment.SetBackgroundImage(index);
// //cell.CellComment = (comment);
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 1, rowIndex, imageInCellColumns + 1, rowIndex + imageInCellRows);
// sheetPatriarch.CreatePicture(anchor, index);
// if (!hasRecordLink)
// {
// NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex, 1);
// dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
// hasRecordLink = true;
// }
// rowIndex += (int)Math.Ceiling(imageHeight / cellHeight) + 1;
// }
// else
// {
// cell.SetCellValue("图片不存在");
// }
//}
#endregion
}
if (defectCountInFrame == 0)
{
workbook.RemoveSheetAt(sheetIndex);
}
}
#endregion
#region MapOperator
sheet = workbook.GetSheet(ExcelText.SheetName_MapOperator);
if (ReportModel != 1)
{
sheet.DefaultColumnWidth = 1;
}
//sheet = workbook.GetSheetAt(1);
rowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
rowIndex++;
//5、检测结果样例
Dictionary <InspectionResult, ICellStyle> dict_result_style = new Dictionary <InspectionResult, ICellStyle>();
ICellStyle exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Green;
dict_result_style.Add(InspectionResult.OK, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.OK), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Red;
dict_result_style.Add(InspectionResult.NG, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.NG), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Yellow;
dict_result_style.Add(InspectionResult.N2K, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.N2K), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Orange;
dict_result_style.Add(InspectionResult.K2N, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.K2N), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.SkyBlue;
dict_result_style.Add(InspectionResult.SKIP, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.SKIP), exampleStyle);
rowIndex++;
//6、分盘绘制图谱
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
ICellStyle centerAlignmentStyle = workbook.CreateCellStyle();
centerAlignmentStyle.Alignment = HorizontalAlignment.Center;
int sheetContinuedIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (inspectionDataReportMatrix.ColumnCount < 254)
{
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值条号
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
//创建二维码列表
if (ReportModel == 1)
{
code2drowIndex = rowIndex + inspectionDataReportMatrix.RowCount + 2;
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(code2drowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值二维码
sheet.GetRow(code2drowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", "二维码", inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(code2drowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
//for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
//{
// ICell cell = sheet.GetRow(code2drowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
// //cell.SetCellValue((i + 1).ToString());
// cell.CellStyle = centerAlignmentStyle;
//}
foreach (InspectionDataReport data1 in inspectionDataReportMatrix)
{
if (data1 == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(code2drowIndex + data1.RowIndex).GetCell(data1.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(code2drowIndex + data1.ColumnIndex).GetCell(data1.RowIndex + 2);
}
cell.SetCellValue(data1.Code2D);
}
}
}
else
{
if (rowIndex + inspectionDataReportMatrix.ColumnCount + 1 >= 32757)
{
sheetContinuedIndex++;
sheet = workbook.CreateSheet(ExcelText.SheetName_MapOperator + " " + ExcelText.Continued + sheetContinuedIndex.ToString());
rowIndex = 0;
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.RowCount + 2; j++)
{
createRow.CreateCell(j);
}
}
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.ColumnCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
}
foreach (InspectionDataReport data in inspectionDataReportMatrix)
{
if (data == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(rowIndex + data.RowIndex).GetCell(data.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(rowIndex + data.ColumnIndex).GetCell(data.RowIndex + 2);
}
cell.CellStyle = dict_result_style[data.InspectionResult];
if (data.InspectionResult == InspectionResult.NG)
{
//string[] defectTypes = new string[data.List_DefectData.Count];
//for (int i = 0; i < data.List_DefectData.Count; i++)
//{
// defectTypes[i] = data.List_DefectData[i].DefectTypeIndex.ToString();
//}
string[] noRepeatDefectTyoes = new string[data.Priority_DetectType.Count];
for (int i = 0; i < data.Priority_DetectType.Count; i++)
{
noRepeatDefectTyoes[i] = data.Priority_DetectType[i].ToString();
}
//string[] noRepeatDefectTyoes = data.List_DefectData.Select(d => d.DefectTypeIndex.ToString()).Distinct().ToArray();
cell.SetCellValue(string.Join(";", noRepeatDefectTyoes));
}
if (data.InspectionResult == InspectionResult.NG || data.InspectionResult == InspectionResult.N2K || data.InspectionResult == InspectionResult.K2N)
{
if (data.ExcelDefectImageLink == null)
{
continue;
}
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = data.ExcelDefectImageLink;
cell.Hyperlink = link;
}
#region 对NG图 以及 N2K图添加批注 鼠标悬停可以显示图片 已屏蔽
//if (data.InspectionResult == InspectionResults.NG || data.InspectionResult == InspectionResults.N2K)
//{
// //int defectCount = data.List_DefectData.Count;
// //System.Drawing.Image[] images = new System.Drawing.Image[defectCount];
// System.Drawing.Image[] images = new System.Drawing.Image[data.List_GeneralImageTempPath.Count];
// double imageMaxWidth = 0;
// double imageTotalHeight = 0;
// //for (int i = 0; i < defectCount; i++)
// //{
// // if (!File.Exists(data.List_DefectData[i].ImageTempPath)) break;
// // byte[] bytesOfImage = File.ReadAllBytes(data.List_DefectData[i].ImageTempPath);
// // using (MemoryStream ms = new MemoryStream(bytesOfImage))
// // {
// // images[i] = System.Drawing.Image.FromStream(ms);
// // }
// // if (images[i].Width > imageMaxWidth)
// // {
// // imageMaxWidth = images[i].Width;
// // }
// // imageTotalHeight += images[i].Height;
// //}
// for (int i = 0; i < images.Count(); i++)
// {
// if (!File.Exists(data.List_GeneralImageTempPath[i])) break;
// byte[] bytesOfImage = File.ReadAllBytes(data.List_GeneralImageTempPath[i]);
// using (MemoryStream ms = new MemoryStream(bytesOfImage))
// {
// images[i] = System.Drawing.Image.FromStream(ms);
// }
// if (images[i].Width > imageMaxWidth)
// {
// imageMaxWidth = images[i].Width;
// }
// imageTotalHeight += images[i].Height;
// }
// if (imageMaxWidth == 0 || imageTotalHeight == 0) continue;
// Bitmap concatImage = new Bitmap((int)imageMaxWidth, (int)imageTotalHeight);
// concatImage.SetResolution(300, 300);
// Graphics g = Graphics.FromImage(concatImage);
// float drawStartHeight = 0;
// for (int i = 0; i < images.Count(); i++)
// {
// g.DrawImage(images[i], 0, drawStartHeight);
// drawStartHeight += images[i].Height;
// images[i].Dispose();
// }
// byte[] bytesOfConcatImage;
// using (MemoryStream ms = new MemoryStream())
// {
// concatImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
// bytesOfConcatImage = ms.ToArray();
// }
// int index = workbook.AddPicture(bytesOfConcatImage, PictureType.JPEG);
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// //像素 = (Excel的行高度 / 72) * DPI
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageMaxWidth / cellWidth);
// int imageInCellRows = (int)(imageTotalHeight / cellHeight);
// double offsetX = (imageMaxWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageTotalHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// IClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// commentAnchor.AnchorType = AnchorType.MoveDontResize;
// HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// comment.SetBackgroundImage(index);
// cell.CellComment = comment;
// HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
// link.Address = data.ExcelDefectImageLink;
// cell.Hyperlink = link;
// //NPOI.SS.Util.CellReference cr = new NPOI.SS.Util.CellReference("A1");
// //ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
// //IFont hlink_font = hssfworkbook.CreateFont();
// //hlink_font.Underline = FontUnderlineType.Single;
// //hlink_font.Color = HSSFColor.Blue.Index;
// //hlink_style.SetFont(hlink_font);
// //cell.CellStyle = (hlink_style);
// //HSSFCell picIndexCell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(picColumnIndex);
// //picIndexCell.SetCellValue(string.Format("{0}-{1}", data.Row + 1, data.Column + 1));
// //picIndexCell.CellStyle = centerAlignmentStyle;
// //if (!data.PicturePath.Equals(string.Empty))
// //{
// // if (File.Exists(data.PicturePath))
// // {
// // byte[] bytes = File.ReadAllBytes(data.PicturePath);
// // int index = workbook.AddPicture(bytes, PictureType.JPEG);
// // System.Drawing.Image image;
// // using (MemoryStream ms = new MemoryStream(bytes))
// // {
// // image = System.Drawing.Image.FromStream(ms);
// // }
// // double imageWidth = image.Width;
// // double imageHeight = image.Height;
// // image.Dispose();
// // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// // //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// // //像素 = (Excel的行高度 / 72) * DPI
// // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// // int imageInCellColumns = (int)(imageWidth / cellWidth);
// // int imageInCellRows = (int)(imageHeight / cellHeight);
// // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// // HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// // commentAnchor.AnchorType = AnchorType.MoveDontResize;
// // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// // comment.SetBackgroundImage(index);
// // cell.CellComment = (comment);
// // //HSSFClientAnchor anchor = new HSSFClientAnchor(100, 0, 923, 0, picColumnIndex, rowIndex + 1, picColumnIndex, rowIndex + dieDataSQLMatrix.RowCount);
// // //patriarch.CreatePicture(anchor, index);
// // }
// //}
// //else
// //{
// // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(picColumnIndex);
// // cell.SetCellValue("图片不存在");
// // cell.CellStyle = centerAlignmentStyle;
// //}
// //picColumnIndex++;
//}
#endregion
}
if (inspectionDataReportMatrix.ColumnCount < 254)
{
if (ReportModel == 1)
{
rowIndex = code2drowIndex;
}
rowIndex += inspectionDataReportMatrix.RowCount + 2;
}
else
{
rowIndex += inspectionDataReportMatrix.ColumnCount + 2;
}
}
sheet.SetColumnWidth(0, 20 * 256);
#endregion
#region UDD
sheet = workbook.GetSheet(ExcelText.SheetName_UDD);
//sheet = workbook.GetSheetAt(2);
rowIndex = 0;
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectTypeInfoReport.Index.ToString(), defectTypeInfoReport.DefectType);
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
public static EmailExtend.FileAttachment RoomScoreReport(string startTime, string endTime)
{
using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/cleanscore_citizenM.xls"), FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
var ModelList = DbFunction.RoomScoreReport_citizenM(startTime, endTime);
var sheetUser = (HSSFSheet)hssfworkbook.GetSheet("pm");
var summarySheet = (HSSFSheet)hssfworkbook.GetSheet("Summary");
sheetUser.GetRow(0).GetCell(18).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy"));
summarySheet.GetRow(0).GetCell(1).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy"));
int row = 2;
var tabIndex = 2;
var userList = ModelList.Select(x => new { x.UserID, x.UserName }).Distinct().ToList();
userList.ForEach(user => {
var dataRow = 3;
int copyRow = 2;
sheetUser = (HSSFSheet)hssfworkbook.CloneSheet(1);
string sheetName = user.UserName.Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' ');
hssfworkbook.SetSheetName(tabIndex, sheetName);
var RoomList = ModelList.AsParallel().Where(x => x.UserID == user.UserID).OrderBy(x => x.NodeName).ToList();
HSSFRow summaryTimeRow = (HSSFRow)summarySheet.GetRow(0);
summarySheet.CopyRow(copyRow, row + 1);
HSSFRow sRow = (HSSFRow)summarySheet.GetRow(row + 1);
sRow.GetCell(0).SetCellValue(user.UserName);
sRow.GetCell(1).SetCellValue(RoomList.Count());
sRow.GetCell(2).SetCellValue(RoomList.Sum(x => x.Credit));
RoomList.ForEach(item => {
var cleaningType = "";
switch (item.CleaningType)
{
case 0:
cleaningType = "Checkout";
break;
case 1:
cleaningType = "Stayover";
break;
case 2:
cleaningType = "Mandatory Stayover";
break;
}
HSSFRow userRow = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow);
userRow.GetCell(0).SetCellValue(item.NodeName);
userRow.GetCell(1).SetCellValue(item.StartRoomStauts);
userRow.GetCell(2).SetCellValue(item.EndRoomStauts);
userRow.GetCell(3).SetCellValue(cleaningType);
userRow.GetCell(4).SetCellValue(item.CreatorUserName);
userRow.GetCell(5).SetCellValue(item.UserName);
userRow.GetCell(6).SetCellValue(item.ToDoTime);
userRow.GetCell(7).SetCellValue(item.DoingTime);
userRow.GetCell(8).SetCellValue(item.PauseTime);
userRow.GetCell(9).SetCellValue(item.PauseReason);
userRow.GetCell(10).SetCellValue(item.ResumeTime);
userRow.GetCell(11).SetCellValue(item.DoneTime);
userRow.GetCell(12).SetCellValue(item.CleanTime);
userRow.GetCell(13).SetCellValue(item.Credit);
userRow.GetCell(14).SetCellValue(item.AdditionalTaskName);
userRow.GetCell(15).SetCellValue(item.AdditionalTaskCredit);
userRow.GetCell(16).SetCellValue(item.TouchUpStartTime);
userRow.GetCell(17).SetCellValue(item.TouchUpDoneTime);
userRow.GetCell(18).SetCellValue(item.TouchUpMinutes);
userRow.GetCell(19).SetCellValue(item.RoomNote);
dataRow++;
});
HSSFRow sheetLast = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow);
sheetLast.GetCell(11).SetCellValue("Total");
sheetLast.GetCell(12).SetCellValue(RoomList.Sum(x => x.Credit));
sheetUser.RemoveRowAt(2);
row++;
tabIndex++;
});
hssfworkbook.RemoveSheetAt(1);
summarySheet.RemoveRowAt(2);
var fileName = SystemConfig.HotelName + " Clean Score Report_" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime)) + ".xls";
var ms = new MemoryStream();
hssfworkbook.Write(ms);
return(new EmailExtend.FileAttachment()
{
FileContent = ms.ToArray(),
FileName = fileName
});
}
}
public static EmailExtend.FileAttachment SupervisorReport(string startTime, string endTime)
{
using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/supervisorsheet_citizenM.xls"), FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
List <SupervisorSheetModel_citizenM> ModelList = DbFunction.SupervisorListReport_citizenM(startTime, endTime);
var UserList = ModelList.Select(x => x.InspectBy).Distinct().ToList();
var datenow = Convert.ToDateTime(startTime);
//得到 Excel 模板的名称 为 Room 的表格
var sheet0 = (HSSFSheet)hssfworkbook.GetSheetAt(0);
sheet0.GetRow(6).GetCell(3).SetCellValue(ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime), 2) + "-" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(endTime), 2));
var tabIndex = 1;
for (int i = 0; i < UserList.Count; i++)
{
var row = 9;
sheet0 = (HSSFSheet)hssfworkbook.CloneSheet(0);
string sheetName = UserList[i].ToString().Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' ');
hssfworkbook.SetSheetName(tabIndex, sheetName);
var NodeList = ModelList.Where(x => x.InspectBy == sheetName).OrderBy(x => Convert.ToInt32(x.NodeName)).ThenBy(x => Convert.ToInt32(x.NodeName)).ToList();
for (int j = 0; j < NodeList.Count; j++)
{
var cleaningType = "";
switch (NodeList[j].CleaningType)
{
case 0:
cleaningType = "Checkout";
break;
case 1:
cleaningType = "Stayover";
break;
case 2:
cleaningType = "Mandatory Stayover";
break;
}
sheet0.CopyRow(8, row);
HSSFRow fsRow = (HSSFRow)sheet0.GetRow(row);
fsRow.GetCell(0).SetCellValue(NodeList[j].NodeName);
fsRow.GetCell(1).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(2).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(3).SetCellValue(NodeList[j].StartStatusRoom);
fsRow.GetCell(4).SetCellValue(NodeList[j].EndStatusRoom);
fsRow.GetCell(5).SetCellValue(cleaningType);
fsRow.GetCell(6).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(7).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(8).SetCellValue(NodeList[j].DoneBy);
fsRow.GetCell(9).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoingTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(10).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(11).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(12).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoneTime).ToString("dd/MM HH:mm"));
fsRow.GetCell(13).SetCellValue(NodeList[j].InspectBy);
fsRow.GetCell(14).SetCellValue(NodeList[j].InspectRemark);
fsRow.GetCell(15).SetCellValue(NodeList[j].InspectTouchUpRemark);
fsRow.GetCell(16).SetCellValue(NodeList[j].RoomNote);
fsRow.GetCell(17).SetCellValue(NodeList[j].IsSkipped == 1 ? "√" : "");
row++;
}
sheet0.RemoveRowAt(8);
tabIndex++;
}
if (UserList.Count > 0)
{
hssfworkbook.RemoveSheetAt(0);
}
var fileName = SystemConfig.HotelName + " Supervisor Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls";
var ms = new MemoryStream();
hssfworkbook.Write(ms);
return(new EmailExtend.FileAttachment()
{
FileContent = ms.ToArray(),
FileName = fileName
});
}
}
