private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Do you want save ?", "Save", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
ItemEdit.UploadFolder = txtFolderUpload.Text;
ItemEdit.IsFile = chkIsFile.Checked;
ItemEdit.FileName = txtFileName.Text;
ItemEdit.HandlerLink = txtHandlerLink.Text;
ItemEdit.HandlerKey = txtHandlerKey.Text;
ItemEdit.SpreadsheetID = txtSpreadsheetID.Text;
ItemEdit.SpreadsheetName = txtSpreadsheetName.Text;
ItemEdit.ColumnValueStart = HelperExcel.GetColumnFromByRange(txtRangeValue.Text);
ItemEdit.ColumnValueEnd = HelperExcel.GetColumnToByRange(txtRangeValue.Text);
ItemEdit.RowValueStart = HelperExcel.GetRowFromByRange(txtRangeValue.Text);
ItemEdit.RowValueEnd = HelperExcel.GetRowToByRange(txtRangeValue.Text);
ItemEdit.ColumnTitle = HelperExcel.GetColumnByCell(txtCellTitle.Text);
ItemEdit.RowTitle = HelperExcel.GetRowByCell(txtCellTitle.Text);
ItemEdit.LinkData = txtLinkData.Text;
ItemEdit.LinkPush = txtLinkPush.Text;
ItemEdit.FormatPushTitle = txtFormatPushTitle.Text;
ItemEdit.FormatPushBody = txtFormatPushBody.Text;
this.DialogResult = DialogResult.Yes;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Form2_Load(object sender, EventArgs e)
{
try
{
if (ItemEdit == null)
{
btnDelete.Visible = false;
ItemEdit = new DTODataDetail();
}
txtFolderUpload.Text = ItemEdit.UploadFolder;
chkIsFile.Checked = ItemEdit.IsFile;
txtFileName.Text = ItemEdit.FileName;
txtHandlerLink.Text = ItemEdit.HandlerLink;
txtHandlerKey.Text = ItemEdit.HandlerKey;
txtSpreadsheetID.Text = ItemEdit.SpreadsheetID;
txtSpreadsheetName.Text = ItemEdit.SpreadsheetName;
txtRangeValue.Text = HelperExcel.GetRangeName(ItemEdit.RowValueStart, ItemEdit.ColumnValueStart, ItemEdit.RowValueEnd, ItemEdit.ColumnValueEnd);
txtCellTitle.Text = HelperExcel.GetCellName(ItemEdit.RowTitle, ItemEdit.ColumnTitle);
txtLinkData.Text = ItemEdit.LinkData;
txtLinkPush.Text = ItemEdit.LinkPush;
txtFormatPushTitle.Text = ItemEdit.FormatPushTitle;
txtFormatPushBody.Text = ItemEdit.FormatPushBody;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void BntStart_Click(object sender, EventArgs e)
{
string fileName = @"D:\MyProjects\VBA\ThongTinConNguoi.docx";
string fileExcel = @"D:\MyProjects\C#\Kiet\Temp\Kiet\Form.xlsx";
var temp = HelperExcel.GetInfoExcel(fileExcel);
AddFile(temp, @"D:\MyProjects\C#\read-word\Data\mau.docx");
}
// 导出Excel文件
protected void btnExportExcel_Click(object sender, EventArgs e)
{
int intContractId = Convert.ToInt32(ViewState["ContractId"]);
string[] aryExcel = BllInventoryContract.setExcel(intContractId);
// 设置压缩文件的下载链接
hlDownloadExcel.NavigateUrl = HelperExcel.SetExcelZip(aryExcel);
hlDownloadExcel.Visible = true;
}
// 导出入库单货品清单以便打印的Excel文件
public static string[] setExcel(int intContractId)
{
// 得到此入库单的信息
ModelSalesContract model = getById(intContractId);
string strCompanyName;
if (model.id_company > 0)
{
strCompanyName = BllSalesCompany.getById(model.id_company).name;
}
else
{
strCompanyName = "预入库";
}
string strYear = model.time_sign.Year.ToString();
string strMonth = model.time_sign.Month.ToString();
string strDay = model.time_sign.Day.ToString();
string strDateShow = " " + strYear + " " + strMonth + " " + strDay;
// 根据入库单ID得到此入库单下所有的入库货品记录，转成DataTable作为Excel文件的数据源
DataTable objDTAll = BllSalesGoods.getAll(intContractId);
DataSet objDS = HelperUtility.splitDataTable(objDTAll, 5);
string[] aryReturn = new string[objDS.Tables.Count];
DataTable objDT;
string strExcelTemplateFileName = "/Excel/Template/01入库单.xlsx";
string strExcelOutFileName;
for (int i = 0; i < objDS.Tables.Count; i++)
{
// 先对DataTable里的数据进行改造
objDT = objDS.Tables[i];
objDT.TableName = "DataTable";
WorkbookDesigner objDesigner = new WorkbookDesigner();
objDesigner.SetDataSource("SalesCompany", " " + strCompanyName);
objDesigner.SetDataSource("DateShow", strDateShow);
objDesigner.SetDataSource(objDT);
// 设置要输出的每个Excel文件的文件名
strExcelOutFileName = "/Excel/Export/入库单[" + strCompanyName +
model.time_sign.ToString("yyMMdd") + "]-" + (i + 1) + ".xlsx";
// 根据以上参数生成excel文件，并输出生成的excel文件路径
HelperExcel.ExportExcelByTemplate(objDesigner, strExcelTemplateFileName, strExcelOutFileName);
aryReturn[i] = strExcelOutFileName;
// 处理完成后把表名改成别的名字，DS中不能有重名的Table
objDT.TableName = "DataTable" + i;
}
return(aryReturn);
}
public static string[] setExcel(int intContractId)
{
// 根据盘点单ID得到此盘点单下所有的盘点记录，转成DataTable作为Excel文件的数据源
DataTable objDT = BllInventoryRecord.getAll(intContractId);
objDT.TableName = "TableInventory";
DataRow objDR;
objDT.Columns.Add(new DataColumn("id_row", typeof(int)));
objDT.Columns.Add(new DataColumn("temp1", typeof(string)));
objDT.Columns.Add(new DataColumn("temp2", typeof(string)));
for (int i = 0; i < objDT.Rows.Count; i++)
{
objDR = objDT.Rows[i];
objDR["id_row"] = i + 1;
objDR["temp1"] = Convert.ToDateTime(objDR["time_sign"]).ToString("yyyy-MM-dd");
objDR["temp2"] = Convert.ToDateTime(objDR["validity_period"]).ToString("yyyy-MM-dd");
}
objDT.Columns.Remove(objDT.Columns["time_sign"]);
objDT.Columns.Remove(objDT.Columns["validity_period"]);
objDT.Columns["temp1"].ColumnName = "time_sign";
objDT.Columns["temp2"].ColumnName = "validity_period";
// 得到此盘点单的信息，设置要输出的Excel文件的文件名
ModelInventoryContract model = getById(intContractId);
string strDateShow = "盘点单 " +
model.time_start.ToString("yyyy年MM月dd日") +
" 至 " +
model.time_end.ToString("yyyy年MM月dd日");
string strFileName = "盘点单[" +
model.time_start.ToString("yyMMdd") +
"-" +
model.time_end.ToString("yyMMdd") +
"]-1.xlsx";
string strExcelTemplateFileName = "/Excel/Template/03盘点单.xlsx";
string strExcelOutFileName = "/Excel/Export/" + strFileName;
// 根据以上参数生成excel文件，并输出生成的excel文件路径
WorkbookDesigner objDesigner = new WorkbookDesigner();
objDesigner.SetDataSource("DateShow", strDateShow);
objDesigner.SetDataSource(objDT);
HelperExcel.ExportExcelByTemplate(objDesigner, strExcelTemplateFileName, strExcelOutFileName);
string[] aryReturn = new string[1];
aryReturn[0] = strExcelOutFileName;
return(aryReturn);
}
/// <summary>
/// 导出记录出库单货品清单的Excel文件
/// </summary>
/// <param name="intContractId">出库单id号</param>
/// <returns>5条记录为一个excel文件，每个文件的地址存到数组里输出</returns>
public static string[] setExcel(int intContractId)
{
// 得到此出库单的信息
ModelCheckoutContract model = getById(intContractId);
// 得到收货单位名称
string strNameUnit = model.name_unit;
string[] aryUnit = strNameUnit.Split(',');
// 得到收货部门科室名称
string strNameDepartment = model.name_department;
// 设置出库单时间
string strYear = model.time_create.Year.ToString();
string strMonth = model.time_create.Month.ToString();
string strDay = model.time_create.Day.ToString();
string strDateShow = " " + strYear + " " + strMonth + " " + strDay;
// 根据出库单ID得到此出库单下所有的出库货品记录，转成DataTable作为Excel文件的数据源
DataTable objDTAll = BllCheckoutRecord.getAll(intContractId);
DataSet objDS = HelperUtility.splitDataTable(objDTAll, 5);
List <string> listReturn = new List <string>();
DataTable objDT;
string strExcelTemplateFileName, strExcelOutFileName;
// 不同的收货单位要设置不同的模板，二院的肯定要生成
if ("信州区第二人民医院".Equals(aryUnit[0]))
{
for (int i = 0; i < objDS.Tables.Count; i++)
{
// 先对DataTable里的数据进行改造
objDT = objDS.Tables[i];
objDT.TableName = "DataTable";
WorkbookDesigner objDesigner = new WorkbookDesigner();
objDesigner.SetDataSource("NameUnit", aryUnit[0]);
objDesigner.SetDataSource("NameDepartment", strNameDepartment);
objDesigner.SetDataSource("DateShow", strDateShow);
objDesigner.SetDataSource(objDT);
strExcelTemplateFileName = "/Excel/Template/02出库单.xlsx";
// 设置要输出的每个Excel文件的文件名
strExcelOutFileName = "/Excel/Export/出库单[区二院-" + strNameDepartment + "-" +
model.time_create.ToString("yyMMdd") + "]-" + (i + 1) + ".xlsx";
// 根据以上参数生成excel文件，并输出生成的excel文件路径
HelperExcel.ExportExcelByTemplate(objDesigner, strExcelTemplateFileName, strExcelOutFileName);
listReturn.Add(strExcelOutFileName);
// 处理完成后把表名改成别的名字，DS中不能有重名的Table
objDT.TableName = "DataTable" + i;
}
}
// 如果社区的也要打就在此生成
if ("东市街道社区卫生服务中心".Equals(aryUnit[1]))
{
for (int i = 0; i < objDS.Tables.Count; i++)
{
// 先对DataTable里的数据进行改造
objDT = objDS.Tables[i];
objDT.TableName = "DataTable";
WorkbookDesigner objDesigner = new WorkbookDesigner();
objDesigner.SetDataSource("NameUnit", aryUnit[1]);
objDesigner.SetDataSource("NameDepartment", strNameDepartment);
objDesigner.SetDataSource("DateShow", strDateShow);
objDesigner.SetDataSource(objDT);
strExcelTemplateFileName = "/Excel/Template/02出库单.xlsx";
// 设置要输出的每个Excel文件的文件名
strExcelOutFileName = "/Excel/Export/出库单[东市-" + strNameDepartment + "-" +
model.time_create.ToString("yyMMdd") + "]-" + (i + 1) + ".xlsx";
// 根据以上参数生成excel文件，并输出生成的excel文件路径
HelperExcel.ExportExcelByTemplate(objDesigner, strExcelTemplateFileName, strExcelOutFileName);
listReturn.Add(strExcelOutFileName);
// 处理完成后把表名改成别的名字，DS中不能有重名的Table
objDT.TableName = "DataTable" + i;
}
}
return(listReturn.ToArray());
}
