public void TestGetNextWorkDay()
{
DateTime dateTime = DateTime.Parse("2017/9/30");
DateTime nextWorkDate = DateTimeFormator.GetNextWorkDate(dateTime);
string[] infos = "a".Split('|');
}
private void btnGetTodayExcel_Click(object sender, EventArgs e)
{
List <Visa> visaList =
_bllVisa.GetModelList(" (EntryTime between '" + DateTimeFormator.DateTimeToString(DateTime.Now) + " 00:00:0.000' and " + " '" +
DateTimeFormator.DateTimeToString(DateTime.Now) +
" 23:59:59.999') and Types='个签'");
_bllActionRecords.CheckStatesAndRemove(visaList, Common.Enums.ActType._02TypeInData); //去除还没有做资料的
if (visaList.Count <= 0)
{
MessageBoxEx.Show("今日没有报表需要生成!");
return;
}
List <List <VisaInfo> > visaInfoList = new List <List <VisaInfo> >();
for (int i = 0; i < visaList.Count; i++)
{
List <VisaInfo> list = _bllVisaInfo.GetModelList(" visa_id = '" + visaList[i].Visa_id.ToString() + "' ");
visaInfoList.Add(list);
}
FrmTodaySubmit frm = new FrmTodaySubmit(visaList, visaInfoList);
frm.ShowDialog();
//ExcelGenerator.GetEverydayExcel(visaList, visaInfoList);
}
private void FrmAddSteelInfo_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_is4Modify)
{
//把选中的加载到这里面
txtName.Text = _model.Name;
txtSize.Text = _model.Size;
txtTexture.Text = _model.Texture;
txtProducePlace.Text = _model.ProducePlace;
txtPrice.Text = _model.Price.ToString();
txtFluctuation.Text = _model.Fluctuation.ToString();
txtRemark.Text = _model.Remark;
txtState.Text = _model.State;
txtInfoTime.Text = DateTimeFormator.DateTimeToString(_model.InfoTime, DateTimeFormator.TimeFormat.Type14LongTime1);
this.Text = "修改钢材";
}
}
private void ModelToCtrls(Model.VisaInfo model)
{
if (model == null)
{
return;
}
txtName.Text = model.Name;
txtEnglishName.Text = model.EnglishName;
txtSex.Text = model.Sex;
txtIssuePlace.Text = model.IssuePlace;
txtResidence.Text = model.Residence;
txtBirthday.Text = DateTimeFormator.DateTimeToString(model.Birthday);
txtOccupation.Text = model.Occupation;
txtMarrige.Text = model.Marriaged;
txtIdentification.Text = model.Identification;
txtFinancialCapacity.Text = model.FinancialCapacity;
txtPassportNo.Text = model.PassportNo;
txtLicenseTime.Text = DateTimeFormator.DateTimeToString(model.LicenceTime);
txtExpireDate.Text = DateTimeFormator.DateTimeToString(model.ExpiryDate);
txtBirthPlace.Text = model.Birthplace;
txtGroupNo.Text = model.GroupNo;
txtDepartureRecord.Text = model.DepartureRecord;
txtPhone.Text = model.Phone;
txtClient.Text = model.Client;
txtSalesPerson.Text = model.Salesperson;
cbTypes.Text = model.Types;
txtReturnTime.Text = DateTimeFormator.DateTimeToString(model.ReturnTime);
cbCountry.Text = model.Country;
}
private void FrmTimeSpanChoose_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
//初始化为今天
txtTime.Text = DateTimeFormator.DateTimeToString(DateTime.Now, DateTimeFormator.TimeFormat.Type14LongTime1);
}
private void FrmTimeSpanChoose_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
//初始化为今天
txtFrom.Text = DateTimeFormator.DateTimeToString(DateTime.Now, DateTimeFormator.TimeFormat.Type07DayStart);
txtTo.Text = DateTimeFormator.DateTimeToString(DateTime.Now, DateTimeFormator.TimeFormat.Type08DayEnd);
}
/// <summary>
/// 从已有visaModel设置窗口(已经设置了团号)
/// </summary>
private void InitFrmFromVisaModel()
{
if (_visaModel == null)
{
return;
}
//查询得到所有的属于这个团的用户
_list = _bllVisaInfo.GetModelList(" Visa_id = '" + _visaModel.Visa_id.ToString() + "'");
//根据list加载列表
lvOut.Items.Clear();
lvIn.Items.Clear();
for (int i = 0; i < _list.Count; i++)
{
ListViewItem liv = new ListViewItem(_list[i].Name);
//ListViewItem.ListViewSubItem livSubItem1 = new ListViewItem.ListViewSubItem(liv, DateTimeFormator.DateTimeToString(_list[i].EntryTime));
//ListViewItem.ListViewSubItem livSubItem2 = new ListViewItem.ListViewSubItem(liv, _list[i].IssuePlace);
ListViewItem.ListViewSubItem livSubItem3 = new ListViewItem.ListViewSubItem(liv, _list[i].PassportNo);
//liv.SubItems.Add(livSubItem1);
//liv.SubItems.Add(livSubItem2);
liv.SubItems.Add(livSubItem3);
liv.Tag = _list[i];
lvIn.Items.Add(liv); //这里是默认进入的在里面
}
//初始化团号
UpdateGroupNo();
//初始化dgv
UpdateDgvAndListViaListView();
//初始化备注项
for (int i = 0; i < dgvGroupInfo.Rows.Count; i++)
{
dgvGroupInfo.Rows[i].Cells["Remark"].Value = _visaModel.Remark;
}
//初始数据项
txtDepartureTime.Text = DateTimeFormator.DateTimeToString(_visaModel.PredictTime);
cbCountry.Text = _visaModel.Country;
txtSalesPerson.Text = _visaModel.SalesPerson;
txtSubmitTime.Text = DateTimeFormator.DateTimeToString(_visaModel.SubmitTime);
txtInTime.Text = DateTimeFormator.DateTimeToString(_visaModel.InTime);
txtOutTime.Text = DateTimeFormator.DateTimeToString(_visaModel.OutTime);
txtClient.Text = _visaModel.Client;
txtDepartureType.Text = _visaModel.DepartureType;
txtSubmitCondition.Text = _visaModel.SubmitCondition;
txtFetchType.Text = _visaModel.FetchCondition;
//txtTypeInPerson.Text = _visaModel.TypeInPerson;
//txtTypeInPerson.Text = GlobalUtils.LoginUser.UserName; //在Frm load里面设置，因为都要设置操作员
txtCheckPerson.Text = _visaModel.CheckPerson;
chbIsUrgent.Checked = _visaModel.IsUrgent;
txtPerson.Text = _visaModel.Person;
}
private void btnTimeSpanChoose_Click(object sender, EventArgs e)
{
FrmTimeSpanChoose frm = new FrmTimeSpanChoose();
if (frm.ShowDialog() == DialogResult.Cancel)
{
return;
}
txtSchEntryTimeFrom.Text = DateTimeFormator.DateTimeToString(frm.TimeSpanFrom, DateTimeFormator.TimeFormat.Type14LongTime1);
txtSchEntryTimeTo.Text = DateTimeFormator.DateTimeToString(frm.TimeSpanTo, DateTimeFormator.TimeFormat.Type14LongTime1);
}
/// <summary>
/// 从已有visaModel设置窗口(已经设置了团号)
/// </summary>
private void InitFrmFromVisaModel()
{
if (_visaModel == null)
{
return;
}
//查询得到所有的属于这个团的用户
_list = _bllVisaInfo.GetModelList(" Visa_id = '" + _visaModel.Visa_id.ToString() + "'");
//根据list加载列表
lvOut.Items.Clear();
lvIn.Items.Clear();
for (int i = 0; i < _list.Count; i++)
{
ListViewItem liv = new ListViewItem(_list[i].Name);
//ListViewItem.ListViewSubItem livSubItem1 = new ListViewItem.ListViewSubItem(liv, DateTimeFormator.DateTimeToString(_list[i].EntryTime));
//ListViewItem.ListViewSubItem livSubItem2 = new ListViewItem.ListViewSubItem(liv, _list[i].IssuePlace);
ListViewItem.ListViewSubItem livSubItem3 = new ListViewItem.ListViewSubItem(liv, _list[i].PassportNo);
//liv.SubItems.Add(livSubItem1);
//liv.SubItems.Add(livSubItem2);
liv.SubItems.Add(livSubItem3);
liv.Tag = _list[i];
lvIn.Items.Add(liv); //这里是默认进入的在里面
}
//初始化团号
txtGroupNo.Text = _visaModel.GroupNo;
//初始化dgv
UpdateDgvAndListViaListView();
////初始化备注项
//for (int i = 0; i < dgvGroupInfo.Rows.Count; i++)
//{
// dgvGroupInfo.Rows[i].Cells["Remark"].Value = _visaModel.Remark;
//}
//初始数据项
txtDepartureTime.Text = DateTimeFormator.DateTimeToString(_visaModel.PredictTime);
cbCountry.Text = _visaModel.Country;
txtSalesPerson.Text = _visaModel.SalesPerson;
txtClient.Text = _visaModel.Client;
chbIsUrgent.Checked = _visaModel.IsUrgent;
txtPerson.Text = _visaModel.Person;
}
private void FrmAddSaleInfo_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_purchaseInfoModel != null)
{
InitCtrlsByPurchaseInfoModel();
}
if (_is4Modify)
{
//把选中的加载到这里面
txtJianChiLv.Text = DecimalHandler.DecimalToPercent(_model.JianChiLv);
txtArrivalTime.Text = DateTimeFormator.DateTimeToString(_model.ArrivalTime);
txtSettleTime.Text = DateTimeFormator.DateTimeToString(_model.SettleTime);
txtSupplier.Text = _model.Supplier;
txtBrand.Text = _model.Brand;
txtTransportWay.Text = _model.TransportWay;
txtSize.Text = _model.Size;
txtTexture.Text = _model.Texture;
txtAmount.Text = DecimalHandler.DecimalToString(_model.Amount);
txtMargin.Text = DecimalHandler.DecimalToString(_model.Margin);
txtOnlinePrice.Text = DecimalHandler.DecimalToString(_model.OnlinePrice);
txtFluctuation1.Text = DecimalHandler.DecimalToString(_model.Fluctuation1);
txtFluctuation2.Text = DecimalHandler.DecimalToString(_model.Fluctuation2);
txtSalePrice.Text = DecimalHandler.DecimalToString(_model.SalePrice);
txtTotalSale.Text = DecimalHandler.DecimalToString(_model.TotalSale);
txtMarginRate.Text = DecimalHandler.DecimalToPercent(_model.MarginRate);
txtCorporation.Text = _model.Corporation;
txtProject.Text = _model.Project;
txtTieXi.Text = DecimalHandler.DecimalToString(_model.TieXi);
txtLiXi.Text = DecimalHandler.DecimalToString(_model.LiXi);
this.Text = "修改销售信息";
}
AddAutoUpdateEvents();
}
//private void 金桥大名单ToolStripMenuItem_Click(object sender, EventArgs e)
//{
// var visainfos = GetDgvSelList();
// List<string> list = new List<string>();
// for (int i = 0; i < visainfos.Count; i++)
// {
// list.Add(visainfos[i].Name);
// }
// DocGenerator docGenerator = new DocGenerator(DocGenerator.DocType.Type01JinQiaoList);
// docGenerator.Generate(list);
//}
//TODO:增加进度条显示
private void 外领担保函ToolStripMenuItem_Click(object sender, EventArgs e)
{
//DocComGenerator docComGenerator = new DocComGenerator(DocComGenerator.DocType.Type02WaiLingDanBaohan);
GlobalUtils.DocDocxGenerator.SetDocType(DocDocxGenerator.DocType.Type02WaiLingDanBaohan);
var visainfos = GetDgvSelList();
if (this.dataGridView1.SelectedRows.Count > 1)
{
//多余一条的时候生成二维list用于打印
List <List <string> > stringinfos = new List <List <string> >();
for (int i = 0; i < visainfos.Count; i++)
{
List <string> list = new List <string>();
list.Add(visainfos[i].Name);
list.Add(visainfos[i].PassportNo);
list.Add(visainfos[i].IssuePlace);
list.Add(DateTimeFormator.DateTimeToStringOfChinese(DateTime.Today));
stringinfos.Add(list);
}
//多余1条的时候选择保存文件夹
string path = GlobalUtils.OpenBrowseFolderDlg();
if (string.IsNullOrEmpty(path))
{
return;
}
GlobalUtils.DocDocxGenerator.GenerateBatch(stringinfos, path);
}
else //一条单独的时候就直接获取就行
{
//生成需要替换的list
List <string> list = new List <string>();
list.Add(visainfos[0].Name);
list.Add(visainfos[0].PassportNo);
list.Add(visainfos[0].IssuePlace);
list.Add(DateTimeFormator.DateTimeToStringOfChinese(DateTime.Today));
GlobalUtils.DocDocxGenerator.Generate(list);
}
}
/// <summary>
/// 每日个签送钱客人情况表
/// </summary>
/// <param name="visaList"></param>
/// <param name="visaInfoList"></param>
/// <returns></returns>
public static bool GetEverydayExcel(List <Model.Visa> visaList, List <List <VisaInfo> > visaInfoList)
{
//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("");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 10 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 10 * 256);
sheet.SetColumnWidth(5, 13 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 35 * 256);
//3.插入行和单元格
int rowNum = 0;
for (int i = 0; i != visaList.Count; ++i)
{
for (int j = 0; j < visaInfoList[i].Count; j++)
{
++rowNum;
row = sheet.CreateRow(rowNum);
row.CreateCell(0).SetCellValue(rowNum);
row.CreateCell(1).SetCellValue(visaInfoList[i][j].Name);
row.CreateCell(2).SetCellValue(visaInfoList[i][j].IssuePlace);
string residence = visaInfoList[i][j].Residence;
if (visaInfoList[i][j].Residence.Contains(" "))
{
residence = visaInfoList[i][j].Residence.Split(' ')[0];
if (residence.EndsWith("省") || residence.EndsWith("市"))
{
residence = residence.Substring(0, residence.Length - 1);
}
}
row.CreateCell(3).SetCellValue(residence);
row.CreateCell(4).SetCellValue(visaList[i].DepartureType);
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(visaInfoList[i][j].ReturnTime)); //归国时间先不设置
row.CreateCell(6).SetCellValue(visaList[i].Remark);
row.CreateCell(7).SetCellValue(visaInfoList[i][j].Identification);
}
//创建单元格
//设置行高
//row.HeightInPoints = 50;
//设置值
}
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.SetFont(font);
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合并单元格
int dp = 1;
for (int i = 0; i != visaList.Count; ++i)
{
sheet.AddMergedRegion(new CellRangeAddress(dp, dp + visaInfoList[i].Count - 1, 6, 6));
//单独处理合并区域的单元格格式
ICellStyle mergeStyle = wkbook.CreateCellStyle();
mergeStyle.SetFont(font);
mergeStyle.VerticalAlignment = VerticalAlignment.Center;
mergeStyle.Alignment = HorizontalAlignment.Center;
mergeStyle.BorderTop = BorderStyle.Thin;
mergeStyle.BorderBottom = BorderStyle.Thin;
mergeStyle.BorderLeft = BorderStyle.Thin;
mergeStyle.BorderRight = BorderStyle.Thin;
sheet.GetRow(dp).Cells[6].CellStyle = mergeStyle;
dp += visaInfoList[i].Count;
}
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg("每日送签客人情况表.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
private void btnShowToday_Click(object sender, EventArgs e)
{
txtSchEntryTimeFrom.Text = DateTimeFormator.DateTimeToString(DateTime.Today);
txtSchEntryTimeTo.Text = DateTimeFormator.DateTimeToString(DateTime.Today);
btnSearch_Click(null, null);
}
private void btnYestoday_Click(object sender, EventArgs e)
{
txtFrom.Text = DateTimeFormator.DateTimeToString(DateTime.Now.AddDays(-1.0), DateTimeFormator.TimeFormat.Type07DayStart);
txtTo.Text = DateTimeFormator.DateTimeToString(DateTime.Now, DateTimeFormator.TimeFormat.Type08DayEnd);
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSaleInfoReport(List <Model.SaleInfo> list)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("采购信息");
//2.1创建表头
IRow row = sheet.CreateRow(0);
//row.HeightInPoints = 20;
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("送货重量");
row.CreateCell(11).SetCellValue("价差");
row.CreateCell(12).SetCellValue("网价");
row.CreateCell(13).SetCellValue("浮动1");
row.CreateCell(14).SetCellValue("浮动1");
row.CreateCell(15).SetCellValue("贴息");
row.CreateCell(16).SetCellValue("利息");
row.CreateCell(17).SetCellValue("销售单价");
row.CreateCell(18).SetCellValue("销总金额");
row.CreateCell(19).SetCellValue("利润率");
//row.CreateCell(19).SetCellValue("付宇汇通日期");
//row.CreateCell(20).SetCellValue("付款金额");
//row.CreateCell(19).SetCellValue("开票日期");
//row.CreateCell(20).SetCellValue("发票金额");
//2.2设置列宽度
sheet.SetColumnWidth(0, 10 * 256); //时间
sheet.SetColumnWidth(1, 10 * 256); //对账
sheet.SetColumnWidth(2, 10 * 256); //开票日期
sheet.SetColumnWidth(3, 10 * 256); //发票
sheet.SetColumnWidth(4, 10 * 256); //收款日期
sheet.SetColumnWidth(5, 10 * 256); //收款
sheet.SetColumnWidth(6, 10 * 256); //收款
sheet.SetColumnWidth(7, 10 * 256); //收款
sheet.SetColumnWidth(8, 10 * 256); //收款
sheet.SetColumnWidth(9, 10 * 256); //收款
sheet.SetColumnWidth(10, 10 * 256); //收款
sheet.SetColumnWidth(11, 10 * 256); //收款
sheet.SetColumnWidth(12, 10 * 256); //收款
sheet.SetColumnWidth(13, 10 * 256); //收款
sheet.SetColumnWidth(14, 10 * 256); //收款
sheet.SetColumnWidth(15, 10 * 256); //收款
sheet.SetColumnWidth(16, 10 * 256); //收款
sheet.SetColumnWidth(17, 10 * 256); //收款
sheet.SetColumnWidth(18, 10 * 256); //收款
sheet.SetColumnWidth(19, 10 * 256); //收款
int digit = GlobalUtils.DecimalDigits;
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Corporation);
row.CreateCell(1).SetCellValue(list[i].Project);
row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToPercent(list[i].JianChiLv));
row.CreateCell(3).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ArrivalTime));
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToString(list[i].SettleTime));
row.CreateCell(5).SetCellValue(list[i].Supplier);
row.CreateCell(6).SetCellValue(list[i].Brand);
row.CreateCell(7).SetCellValue(list[i].TransportWay);
row.CreateCell(8).SetCellValue(list[i].Size);
row.CreateCell(9).SetCellValue(list[i].Texture);
row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(list[i].Amount, digit));
row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(list[i].Margin, digit));
row.CreateCell(12).SetCellValue(DecimalHandler.DecimalToString(list[i].OnlinePrice, digit));
row.CreateCell(13).SetCellValue(DecimalHandler.DecimalToString(list[i].Fluctuation1, digit));
row.CreateCell(14).SetCellValue(DecimalHandler.DecimalToString(list[i].Fluctuation2, digit));
row.CreateCell(15).SetCellValue(DecimalHandler.DecimalToString(list[i].TieXi, digit));
row.CreateCell(16).SetCellValue(DecimalHandler.DecimalToString(list[i].LiXi, digit));
row.CreateCell(17).SetCellValue(DecimalHandler.DecimalToString(list[i].SalePrice, digit));
row.CreateCell(18).SetCellValue(DecimalHandler.DecimalToString(list[i].TotalSale, digit));
row.CreateCell(19).SetCellValue(DecimalHandler.DecimalToPercent(list[i].MarginRate));
}
////统计的一行
//row = sheet.CreateRow(list.Count + 1);
//row.CreateCell(0);
//row.CreateCell(1).SetCellValue("合计");
//row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToString(total[0], 2));
//row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(total[1], 2));
//row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(total[2], 2));
//row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(total[3], 2));
//row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(total[4], 2));
//row.CreateCell(7).SetCellValue(DecimalHandler.DecimalToString(total[5], 2));
//row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(total[6], 2));
//row.CreateCell(9).SetCellValue(DecimalHandler.DecimalToString(total[7], 2));
//row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(total[8], 2));
//row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(total[9], 2));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Right;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
short color = style.FillForegroundColor;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (i == 0)
{
style.FillForegroundColor = HSSFColor.BlueGrey.Index;
style.FillPattern = FillPattern.SolidForeground;
}
else
{
style.FillForegroundColor = color;
style.FillPattern = FillPattern.NoFill;
}
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.ShowSaveFileDlg("销售信息.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSaleBillReport(List <Model.SaleBill> list)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("销售款项");
//2.1创建表头
IRow row = sheet.CreateRow(0);
//row.HeightInPoints = 20;
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("收款金额");
//2.2设置列宽度
sheet.SetColumnWidth(0, 10 * 256); //时间
sheet.SetColumnWidth(1, 10 * 256); //对账
sheet.SetColumnWidth(2, 10 * 256); //开票日期
sheet.SetColumnWidth(3, 10 * 256); //发票
sheet.SetColumnWidth(4, 10 * 256); //收款日期
sheet.SetColumnWidth(5, 10 * 256); //时间
sheet.SetColumnWidth(6, 10 * 256); //对账
sheet.SetColumnWidth(7, 10 * 256); //开票日期
sheet.SetColumnWidth(8, 10 * 256); //发票
int digit = GlobalUtils.DecimalDigits;
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Corporation);
row.CreateCell(1).SetCellValue(list[i].Project);
row.CreateCell(2).SetCellValue(list[i].Supplier);
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].Amount, digit));
row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhang, digit));
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(list[i].InvoiceDate));
row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(list[i].InvoiceNum, digit));
row.CreateCell(7).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ReceiptDate));
row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(list[i].ReceiptNum, digit));
}
////统计的一行
//row = sheet.CreateRow(list.Count + 1);
//row.CreateCell(0);
//row.CreateCell(1).SetCellValue("合计");
//row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToString(total[0], 2));
//row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(total[1], 2));
//row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(total[2], 2));
//row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(total[3], 2));
//row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(total[4], 2));
//row.CreateCell(7).SetCellValue(DecimalHandler.DecimalToString(total[5], 2));
//row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(total[6], 2));
//row.CreateCell(9).SetCellValue(DecimalHandler.DecimalToString(total[7], 2));
//row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(total[8], 2));
//row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(total[9], 2));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Right;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
short color = style.FillForegroundColor;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (i == 0)
{
style.FillForegroundColor = HSSFColor.BlueGrey.Index;
style.FillPattern = FillPattern.SolidForeground;
}
else
{
style.FillForegroundColor = color;
style.FillPattern = FillPattern.NoFill;
}
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.ShowSaveFileDlg("销售款项.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <summary>
/// 导出日本签证时间表
/// </summary>
/// <param name="list"></param>
/// <param name="remark"></param>
/// <param name="groupNo"></param>
/// <returns></returns>
public static bool GetAllCountExcel(List <TravelAgency.Model.Visa> list)
{
//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("其他备注");
//2.2设置列宽度
sheet.SetColumnWidth(0, 60 * 256); //团号");
sheet.SetColumnWidth(1, 15 * 256); //送签日期");
sheet.SetColumnWidth(2, 15 * 256); //出签日期");
sheet.SetColumnWidth(3, 15 * 256); //送签社担当")
sheet.SetColumnWidth(4, 8 * 256); //人数");
sheet.SetColumnWidth(5, 12 * 256); //资料寄出时间
sheet.SetColumnWidth(6, 17 * 256); //销售人员");
sheet.SetColumnWidth(7, 10 * 256); //客户");
sheet.SetColumnWidth(8, 10 * 256); //其他备注");
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].GroupNo);
row.CreateCell(1).SetCellValue(DateTimeFormator.DateTimeToString1(list[i].InTime));
row.CreateCell(2).SetCellValue(DateTimeFormator.DateTimeToString1(list[i].OutTime));
row.CreateCell(3).SetCellValue(list[i].Person);
row.CreateCell(4).SetCellValue(list[i].Number.ToString());
row.CreateCell(5).SetCellValue("");//资料寄出时间先不设置
row.CreateCell(6).SetCellValue(list[i].SalesPerson);
row.CreateCell(7).SetCellValue(list[i].Client);
row.CreateCell(8).SetCellValue(""); //其他备注先不设置
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
//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;
}
}
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg("日本签证时间表.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSailBillCount(List <Model.SaleBill> list)
{
//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("收款");
//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); //收款
decimal duiZhangTotal = 0;
decimal invoiceTotal = 0;
decimal receiptTotal = 0;
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(DateTimeFormator.DateTimeToString(list[i].EntryTime));
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhang, 2));
duiZhangTotal += list[i].DuiZhang ?? 0;
row.CreateCell(2).SetCellValue(DateTimeFormator.DateTimeToString(list[i].InvoiceDate));
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].InvoiceNum, 2));
invoiceTotal += list[i].InvoiceNum ?? 0;
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ReceiptDate));
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(list[i].ReceiptNum, 2));
receiptTotal += list[i].ReceiptNum ?? 0;
}
//统计的一行
row = sheet.CreateRow(list.Count + 1);
row.CreateCell(0).SetCellValue("合计");
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(duiZhangTotal, 2));
row.CreateCell(2);
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(invoiceTotal, 2));
row.CreateCell(4);
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(receiptTotal, 2));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Right;
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.ShowSaveFileDlg(list[0].Corporation + "_" + list[0].Project + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
public static bool GetIndividualVisaExcel(List <TravelAgency.Model.VisaInfo> list, string remark, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请人名单");
//2.1创建表头
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("签证申请人名单");
rowHeader.HeightInPoints = 50;
IRow row = sheet.CreateRow(1);
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("身份确认");
row.CreateCell(11).SetCellValue("经济能力确认");
row.CreateCell(12).SetCellValue("备注");
row.CreateCell(13).SetCellValue("旅行社意见");
row.CreateCell(14).SetCellValue("护照号");
//row.CreateCell(15).SetCellValue("手机号");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256); //编号
sheet.SetColumnWidth(1, 15 * 256); //姓名(中文)
sheet.SetColumnWidth(2, 20 * 256); //姓名(英文)
sheet.SetColumnWidth(3, 5 * 256); //性别
sheet.SetColumnWidth(4, 10 * 256); //护照发行地
sheet.SetColumnWidth(5, 25 * 256); //居住地点
sheet.SetColumnWidth(6, 15 * 256); //出生年月日
sheet.SetColumnWidth(7, 10 * 256); //职业
sheet.SetColumnWidth(8, 10 * 256); //出境记录
sheet.SetColumnWidth(9, 10 * 256); //婚姻
sheet.SetColumnWidth(10, 20 * 256); //身份确认
sheet.SetColumnWidth(11, 25 * 256); //经济能力确认
sheet.SetColumnWidth(12, 10 * 256); //备注
sheet.SetColumnWidth(13, 10 * 256); //旅行社意见
sheet.SetColumnWidth(14, 15 * 256); //护照号
//sheet.SetColumnWidth(15, 15 * 256);//手机号
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 2);
//设置行高
row.HeightInPoints = 100;
//设置值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].EnglishName);
row.CreateCell(3).SetCellValue(list[i].Sex);
row.CreateCell(4).SetCellValue(list[i].IssuePlace);
row.CreateCell(5).SetCellValue(list[i].Residence);
row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
row.CreateCell(7).SetCellValue(list[i].Occupation);
row.CreateCell(8).SetCellValue(list[i].DepartureRecord);
row.CreateCell(9).SetCellValue(list[i].Marriaged);
row.CreateCell(10).SetCellValue(list[i].Identification);
row.CreateCell(11).SetCellValue(list[i].FinancialCapacity);
row.CreateCell(12).SetCellValue(remark);
row.CreateCell(13).SetCellValue(list[i].AgencyOpinion);
row.CreateCell(14).SetCellValue(list[i].PassportNo);
//row.CreateCell(15).SetCellValue(list[i].Phone);
}
//4.2合并单元格
sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12)); //备注列合并
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //表头合并
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true; //文本自动换行
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontHeightInPoints = 12;
style.SetFont(font);
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;
}
}
ICellStyle headerStyle = wkbook.CreateCellStyle();
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
HSSFFont font1 = (HSSFFont)wkbook.CreateFont();
font1.FontHeightInPoints = 15;
headerStyle.SetFont(font1);
sheet.GetRow(0).GetCell(0).CellStyle = headerStyle;
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <summary>
///
/// </summary>
/// <param name="visaInfoList"></param>
/// <param name="visaModel"></param>
/// <param name="visaList"></param>
public static void GetPre8List(List <Model.VisaInfo> visaInfoList, List <Model.Visa> visaList)
{
if (visaInfoList.Count > 8)
{
MessageBoxEx.Show("请选择8个人以下导出!");
return;
}
//READEXCEL
using (FileStream fs = File.OpenRead(GlobalUtils.AppPath + @"\Excel\Templates\template_(前8人）旅行社申请名单表_（表3）_添加占位符.xlsx"))
{
IWorkbook wkbook = new XSSFWorkbook(fs);
ISheet sheet = wkbook.GetSheetAt(0);
IRow row = sheet.GetRow(10);
for (int c = 0; c < row.LastCellNum; ++c)
{
string dtString = DateTimeFormator.DateTimeToString(DateTimeFormator.GetNextWorkDate(DateTime.Now.AddDays(1)));
string[] datearr = dtString.Split('/');
//1.获取每个单元格
if (row.GetCell(c).ToString() == "{1}")
{
row.GetCell(c).SetCellValue(datearr[0].Substring(2, 2));
}
if (row.GetCell(c).ToString() == "{2}")
{
row.GetCell(c).SetCellValue(datearr[1]);
}
if (row.GetCell(c).ToString() == "{3}")
{
row.GetCell(c).SetCellValue(datearr[2]);
}
}
for (int j = 0; j < 8; j++)
{
row = sheet.GetRow(21 + j * 4);
for (int c = 0; c < row.LastCellNum; ++c)
{
if (row.GetCell(c).ToString() == "{" + (4 + j * 3) + "}")
{
if (j < visaInfoList.Count)
{
//外领送签条件不为空
if (IsOutSigned(visaInfoList[j]) && visaList[j] != null && !string.IsNullOrEmpty(visaList[j].SubmitCondition))
{
row.GetCell(c).SetCellValue(visaInfoList[j].Name + "(" + visaList[j].SubmitCondition + ")");
continue;
}
row.GetCell(c).SetCellValue(visaInfoList[j].Name);
}
else
{
row.GetCell(c).SetCellValue(string.Empty);
}
}
}
row = sheet.GetRow(23 + j * 4);
for (int c = 0; c < row.LastCellNum; ++c)
{
if (row.GetCell(c).ToString() == "{" + (5 + j * 3) + "}")
{
if (j < visaInfoList.Count) //是外签的话设置发行地
{
row.GetCell(c).SetCellValue(visaInfoList[j].IssuePlace);
}
else
{
row.GetCell(c).SetCellValue(string.Empty);
}
}
}
//居住地
row = sheet.GetRow(24 + j * 4);
for (int c = 0; c < row.LastCellNum; ++c)
{
if (row.GetCell(c).ToString() == "{" + (6 + j * 3) + "}")
{
if (j < visaInfoList.Count)
{
string residence = visaInfoList[j].Residence;
if (visaInfoList[j].Residence.Contains(" "))
{
residence = visaInfoList[j].Residence.Split(' ')[0];
if (residence.EndsWith("省") || residence.EndsWith("市"))
{
residence = residence.Substring(0, residence.Length - 1);
}
}
row.GetCell(c).SetCellValue(residence);
}
else
{
row.GetCell(c).SetCellValue(string.Empty);
}
}
}
}
string dstName = GlobalUtils.OpenSaveFileDlg("8人申请表.xlsx", "Excel XLSX|*.xlsx");
// If the file name is not an empty string open it for saving.
if (!string.IsNullOrEmpty(dstName))
{
try
{
using (FileStream fs1 = File.OpenWrite(dstName))
{
wkbook.Write(fs1);
}
Process.Start(dstName);
}
catch (Exception)
{
MessageBoxEx.Show("指定文件名的文件正在使用中，无法写入，请关闭后重试!");
}
}
}
}
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));
}
public static bool GetTeamVisaExcelOfJapan(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("职业");
row.CreateCell(11).SetCellValue("联系电话");
row.CreateCell(12).SetCellValue("客户");
row.CreateCell(13).SetCellValue("销售");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256); //序号
sheet.SetColumnWidth(1, 15 * 256); //姓名
sheet.SetColumnWidth(2, 25 * 256); //英文姓名
sheet.SetColumnWidth(3, 5 * 256); //性别
sheet.SetColumnWidth(4, 10 * 256); //出生地
sheet.SetColumnWidth(5, 20 * 256); //出生日期
sheet.SetColumnWidth(6, 20 * 256); //护照号
sheet.SetColumnWidth(7, 10 * 256); //签发地
sheet.SetColumnWidth(8, 20 * 256); //签发日期
sheet.SetColumnWidth(9, 20 * 256); //有效期至
sheet.SetColumnWidth(10, 20 * 256); //职业
sheet.SetColumnWidth(11, 20 * 256); //联系电话
sheet.SetColumnWidth(12, 20 * 256); //客户
sheet.SetColumnWidth(13, 20 * 256); //销售
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].EnglishName);
row.CreateCell(3).SetCellValue(list[i].Sex);
row.CreateCell(4).SetCellValue(list[i].Birthplace);
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
row.CreateCell(6).SetCellValue(list[i].PassportNo);
row.CreateCell(7).SetCellValue(list[i].IssuePlace);
row.CreateCell(8).SetCellValue(DateTimeFormator.DateTimeToString(list[i].LicenceTime));
row.CreateCell(9).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ExpiryDate));
row.CreateCell(10).SetCellValue(list[i].Occupation);
row.CreateCell(11).SetCellValue(list[i].Phone);
row.CreateCell(12).SetCellValue(list[i].Client);
row.CreateCell(13).SetCellValue(list[i].Salesperson);
}
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
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));
}
private void btnPreAMonth_Click(object sender, EventArgs e)
{
txtFrom.Text = DateTimeFormator.DateTimeToString(DateTime.Now.AddMonths(-1), DateTimeFormator.TimeFormat.Type07DayStart);
txtTo.Text = DateTimeFormator.DateTimeToString(DateTime.Now, DateTimeFormator.TimeFormat.Type08DayEnd);
}
