public static bool GetTeamVisaExcelOfThailand(List <TravelAgency.Model.VisaInfo> list, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请名单");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("姓名");
row.CreateCell(1).SetCellValue("英文姓");
row.CreateCell(2).SetCellValue("英文名");
row.CreateCell(3).SetCellValue("性别");
row.CreateCell(4).SetCellValue("出生日期");
row.CreateCell(5).SetCellValue("护照号");
row.CreateCell(6).SetCellValue("签发日期");
row.CreateCell(7).SetCellValue("有效期至");
row.CreateCell(8).SetCellValue("出生地点拼音");
row.CreateCell(9).SetCellValue("签发地点拼音");
row.CreateCell(10).SetCellValue("英文姓名");
//2.2设置列宽度
sheet.SetColumnWidth(0, 20 * 256); //序号
sheet.SetColumnWidth(1, 20 * 256); //姓名
sheet.SetColumnWidth(2, 20 * 256); //英文姓名
sheet.SetColumnWidth(3, 20 * 256); //性别
sheet.SetColumnWidth(4, 20 * 256); //出生地
sheet.SetColumnWidth(5, 20 * 256); //出生日期
sheet.SetColumnWidth(6, 20 * 256); //护照号
sheet.SetColumnWidth(7, 20 * 256); //签发地
sheet.SetColumnWidth(8, 20 * 256); //签发日期
sheet.SetColumnWidth(9, 20 * 256); //有效期至
sheet.SetColumnWidth(10, 20 * 256); //职业
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Name);
row.CreateCell(1).SetCellValue(list[i].EnglishName.Split(' ')[0]);
row.CreateCell(2).SetCellValue(list[i].EnglishName.Split(' ')[1]);
if (list[i].Sex == "男")
{
row.CreateCell(3).SetCellValue("F");
}
else
{
row.CreateCell(3).SetCellValue("M");
}
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].Birthday));
row.CreateCell(5).SetCellValue(list[i].PassportNo);
row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].LicenceTime));
row.CreateCell(7).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].ExpiryDate));
List <string> pinyins = Common.PinyinParse.PinYinConverterHelp.GetTotalPingYin(list[i].Birthplace).TotalPingYin;
row.CreateCell(8).SetCellValue(pinyins[pinyins.Count - 1].ToUpper()); //TODO:这个地方拼音还有点问题，因为可能有多个
pinyins = Common.PinyinParse.PinYinConverterHelp.GetTotalPingYin(list[i].IssuePlace).TotalPingYin;
row.CreateCell(9).SetCellValue(pinyins[pinyins.Count - 1].ToUpper());
row.CreateCell(10).SetCellValue(list[i].EnglishName);
}
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Left;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 13, 13));
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
