void CreateExcel(DataTable dt)
{
string strFilePath = @"C:\Inetpub\wwwroot\wss\VirtualDirectories\8081\wpresources\002.xls";
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile();
objExcelFile.LoadXls(strFilePath);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
GemBox.Spreadsheet.MultipleBorders borderLeft = GemBox.Spreadsheet.MultipleBorders.Left;
GemBox.Spreadsheet.MultipleBorders borderRight = GemBox.Spreadsheet.MultipleBorders.Right;
GemBox.Spreadsheet.MultipleBorders borderBottom = GemBox.Spreadsheet.MultipleBorders.Bottom;
GemBox.Spreadsheet.LineStyle LineSyleThick = GemBox.Spreadsheet.LineStyle.Thick;//粗实线
System.Drawing.Color color = System.Drawing.Color.Black;
for (int i = 0; i < dt.Rows.Count; i++)
{
worksheet1.Rows[i + 1].Cells[0].SetBorders(borderLeft, color, LineSyleThick);
worksheet1.Rows[i + 1].Cells[3].SetBorders(borderRight, color, LineSyleThick);
worksheet1.Rows[i + 1].Cells[0].Value = dt.Rows[i][0];
worksheet1.Rows[i + 1].Cells[1].Value = dt.Rows[i][1];
worksheet1.Rows[i + 1].Cells[2].Value = dt.Rows[i][2];
worksheet1.Rows[i + 1].Cells[3].Value = dt.Rows[i][3];
if (i == (dt.Rows.Count - 1))
{
worksheet1.Rows[i + 1].Cells[0].SetBorders(borderBottom, color, LineSyleThick);
worksheet1.Rows[i + 1].Cells[1].SetBorders(borderBottom, color, LineSyleThick);
worksheet1.Rows[i + 1].Cells[2].SetBorders(borderBottom, color, LineSyleThick);
worksheet1.Rows[i + 1].Cells[3].SetBorders(borderBottom, color, LineSyleThick);
}
}
objExcelFile.SaveXls(@"C:\Inetpub\wwwroot\wss\VirtualDirectories\8081\wpresources\001.xls");//@"C:\Inetpub\wwwroot\wss\VirtualDirectories\8081\wpresources\Temp01.xls");
}
void CreateExcel(DataTable dt)
{
string strSampleFileName = "POReportSample.xls";
string sSaveFileName = "POReport.xls";
string sFullPath = Server.MapPath("/tmpfiles/PurchaseOrder/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
DataColumnCollection dcc = dt.Columns;
SetExcelTitle(dcc, worksheet1);
int iCount = dt.Rows.Count;
worksheet1.Rows[2].InsertCopy((iCount - 3), worksheet1.Rows[2]);
SetContent(dt, worksheet1);
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
/// <summary>
/// 生成excel
/// </summary>
/// <param name="dt"></param>
/// <param name="sExcelName">xx.xls</param>
void CreatExcel(DataTable dt, string sExcelName)
{
if (null == dt || dt.Rows.Count <= 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('无数据！')</script>");
return;
}
string sFileName = sExcelName;
string tmpPath = "/tmpfiles/PurchaseRequest/";
string strPath = Server.MapPath(tmpPath);
DirectoryInfo dinfo = new DirectoryInfo(strPath);
if (!dinfo.Exists)
{
Directory.CreateDirectory(strPath);
}
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile();
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets.Add("Sheet1");
/*
GemBox.Spreadsheet.MultipleBorders borderLeft = GemBox.Spreadsheet.MultipleBorders.Left;
GemBox.Spreadsheet.MultipleBorders borderRight = GemBox.Spreadsheet.MultipleBorders.Right;
GemBox.Spreadsheet.LineStyle LineSyleThick = GemBox.Spreadsheet.LineStyle.Thick;//粗实线*/
System.Drawing.Color color = System.Drawing.Color.Black;
int iRowCount = dt.Rows.Count;
for (int i = 0; i < iRowCount; i++)
{
int iColumnCount = dt.Columns.Count;
for (int c = 0; c < iColumnCount; c++)
{
/*if (c == 0)
{
worksheet1.Rows[i + 1].Cells[c].SetBorders(borderLeft, color, LineSyleThick);
}
if (c == iColumnCount - 1)
{
worksheet1.Rows[i + 1].Cells[c].SetBorders(borderRight, color, LineSyleThick);
}*/
worksheet1.Rows[i + 1].Cells[c].Value = dt.Rows[i][c];
}
}
List<string> listColumnName = new List<string>();
foreach (DataColumn dc in dt.Columns)
{
listColumnName.Add(dc.ColumnName);
}
//worksheet1.Columns[1].Width = 10000;
//worksheet1.Columns[2].Width = 10000;
SetStyle(worksheet1, iRowCount, listColumnName);
string sFileFullName = string.Concat(strPath, sFileName);
objExcelFile.SaveXls(sFileFullName);
SendExcelToClient(sFileFullName, sFileName);
}
void CreateExcel()
{
string sRequestId = WorkflowContext.Current.DataFields["WorkflowNumber"].AsString();
//string sPONOSAP = WorkflowContext.Current.DataFields["SapNO"].AsString();
string sVendor = WorkflowContext.Current.DataFields["Vendor"].AsString();
string sAddress = WorkflowContext.Current.DataFields["VendorAddress"].AsString();
string sCode = WorkflowContext.Current.DataFields["VendorCode"].AsString();
string sCity = WorkflowContext.Current.DataFields["VendorCity"].AsString();
string sPhone = WorkflowContext.Current.DataFields["Phone"].AsString();
string sFax = WorkflowContext.Current.DataFields["Fax"].AsString();
string sVendorPhone = WorkflowContext.Current.DataFields["VendorPhone"].AsString();
string sVendorFax = WorkflowContext.Current.DataFields["VendorFax"].AsString();
string sEmail = WorkflowContext.Current.DataFields["Email"].AsString();
string sVendorMail = WorkflowContext.Current.DataFields["VendorMail"].AsString();
string sBuyer = WorkflowContext.Current.DataFields["Buyer"].AsString();
string sDepartment = WorkflowContext.Current.DataFields["Department"].AsString();
string sVendorNo = WorkflowContext.Current.DataFields["VendorNo"].AsString();
string sVendorContact = WorkflowContext.Current.DataFields["VendorContact"].AsString();
string sPONumber = WorkflowContext.Current.DataFields["PONumber"].AsString();
string sPONumFinance = WorkflowContext.Current.DataFields["PONumFinance"].AsString();
string sIssuedDate = WorkflowContext.Current.DataFields["IssuedDate"].AsString();
string sTotal = WorkflowContext.Current.DataFields["Total"].AsString();
string sSiteInstallFee = WorkflowContext.Current.DataFields["SiteInstallFee"].AsString();
string sPackageCharge = WorkflowContext.Current.DataFields["PackageCharge"].AsString();
string sFreightCost = WorkflowContext.Current.DataFields["FreightCost"].AsString();
string sDiscount = WorkflowContext.Current.DataFields["Discount"].AsString();
string sTaxValue = WorkflowContext.Current.DataFields["TaxValue"].AsString();
string sGrandTotal = WorkflowContext.Current.DataFields["GrandTotal"].AsString();
string sOrderComment1 = WorkflowContext.Current.DataFields["OrderComment1"].AsString();
string sPaymentCondition = WorkflowContext.Current.DataFields["PaymentCondition"].AsString();
string sDeliveryDirections = WorkflowContext.Current.DataFields["DeliveryDirections"].AsString();
string sGuarantee = WorkflowContext.Current.DataFields["Guarantee"].AsString();
string sDeliveryDate = WorkflowContext.Current.DataFields["DeliveryDate"].AsString();
string sOrderComment2 = WorkflowContext.Current.DataFields["OrderComment2"].AsString();
string strSampleFileName = GetSampleExcel(sPONumber); //"PurchaseOrdeFormSample.xls";
string sSaveFileName ="PurchaseOrdeForm.xls";
string sFullPath = Server.MapPath("/tmpfiles/PurchaseOrder/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
//worksheet1.Rows[0].Cells[12].Value = sPONOSAP;
worksheet1.Rows[1].Cells[7].Value = sVendor;
worksheet1.Rows[2].Cells[7].Value = sAddress;
worksheet1.Rows[3].Cells[7].Value = sCode;
worksheet1.Rows[3].Cells[11].Value = sCity;
worksheet1.Rows[4].Cells[1].Value = sPhone;
worksheet1.Rows[4].Cells[4].Value = sFax;
worksheet1.Rows[4].Cells[7].Value = sVendorPhone;
worksheet1.Rows[4].Cells[11].Value = sVendorFax;
worksheet1.Rows[5].Cells[1].Value = sEmail;
worksheet1.Rows[5].Cells[7].Value = sVendorMail;
worksheet1.Rows[6].Cells[1].Value = sBuyer;
worksheet1.Rows[6].Cells[4].Value = sDepartment;
worksheet1.Rows[6].Cells[7].Value = sVendorNo;
worksheet1.Rows[6].Cells[11].Value = sVendorContact;
worksheet1.Rows[8].Cells[0].Value = sPONumber;
worksheet1.Rows[8].Cells[4].Value = sPONumFinance;
worksheet1.Rows[8].Cells[11].Value = sIssuedDate;
worksheet1.Rows[12].Cells[0].Value = sTotal;
worksheet1.Rows[12].Cells[2].Value = sSiteInstallFee;
worksheet1.Rows[12].Cells[4].Value = sPackageCharge;
worksheet1.Rows[12].Cells[6].Value = sFreightCost;
worksheet1.Rows[12].Cells[8].Value = sDiscount;
worksheet1.Rows[12].Cells[9].Value = sTaxValue;
worksheet1.Rows[12].Cells[10].Value = sGrandTotal;
worksheet1.Rows[14].Cells[0].Value = sPaymentCondition;
worksheet1.Rows[14].Cells[3].Value = sDeliveryDirections;
worksheet1.Rows[14].Cells[5].Value = sGuarantee;
worksheet1.Rows[14].Cells[7].Value = sDeliveryDate;
//string sComments = worksheet1.Rows[13].Cells[10].Value.ToString();
//worksheet1.Rows[13].Cells[10].Value = sComments + sOrderComment1;
//string sComments2 = worksheet1.Rows[15].Cells[10].Value.ToString();
//worksheet1.Rows[15].Cells[10].Value = sComments + sOrderComment2;
DataTable dt = PurchaseOrderCommon.GetDataTable(sRequestId);
if (null != dt && dt.Rows.Count > 0)
{
int iRowsCount = dt.Rows.Count;
// worksheet1.Rows[11].InsertEmpty(iRowsCount);
worksheet1.Rows[11].InsertCopy(iRowsCount-1, worksheet1.Rows[10]);
for (int i = 0; i < iRowsCount; i++)
{
if (i == 0 || i == (iRowsCount-1))
{
System.Drawing.Color color = System.Drawing.Color.Black;
GemBox.Spreadsheet.LineStyle LineSyleThick = GemBox.Spreadsheet.LineStyle.Medium;//粗实线
GemBox.Spreadsheet.MultipleBorders borderTop = GemBox.Spreadsheet.MultipleBorders.Top;
GemBox.Spreadsheet.MultipleBorders borderbuttom = GemBox.Spreadsheet.MultipleBorders.Bottom;
GemBox.Spreadsheet.MultipleBorders border;
if (i == 0)
{
border = borderTop;
}
else
{
border = borderbuttom;
}
for (int j = 0; j < 14; j++)
{
worksheet1.Rows[10+i].Cells[j].SetBorders(border, color, LineSyleThick);
}
}
worksheet1.Rows[10 + i].Cells[0].Value = i + 1;
worksheet1.Rows[10 + i].Cells[1].Value = dt.Rows[i]["ItemCode"];
worksheet1.Rows[10 + i].Cells[2].Value = dt.Rows[i]["CostCenter"];
worksheet1.Rows[10 + i].Cells[3].Value = dt.Rows[i]["Description"];
worksheet1.Rows[10 + i].Cells[4].Value = dt.Rows[i]["TotalQuantity"];
worksheet1.Rows[10 + i].Cells[5].Value = dt.Rows[i]["TransQuantity"];
worksheet1.Rows[10 + i].Cells[6].Value = dt.Rows[i]["RequestQuantity"];
worksheet1.Rows[10 + i].Cells[7].Value = dt.Rows[i]["Unit"];
worksheet1.Rows[10 + i].Cells[8].Value = dt.Rows[i]["Currency"];
worksheet1.Rows[10 + i].Cells[9].Value = dt.Rows[i]["UnitPrice"];
worksheet1.Rows[10 + i].Cells[10].Value = dt.Rows[i]["TotalPrice"];
worksheet1.Rows[10 + i].Cells[11].Value = dt.Rows[i]["TaxRate"];
worksheet1.Rows[10 + i].Cells[12].Value = dt.Rows[i]["TaxValue"];
worksheet1.Rows[10 + i].Cells[12].Value = dt.Rows[i]["TaxValue"];
decimal dTotalPrice = 0;
decimal dTaxValue = 0;
decimal dPrice = 0;
if (decimal.TryParse(dt.Rows[i]["TotalPrice"].ToString(), out dTotalPrice) && decimal.TryParse(dt.Rows[i]["TaxValue"].ToString(), out dTaxValue))
{
dPrice = dTotalPrice - dTaxValue;
worksheet1.Rows[10 + i].Cells[13].Value = dPrice;
}
// worksheet1.Rows[11 + i].Cells[15].Value = dt.Rows[i]["AssetClass"];
}
}
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
void CreateExcel()
{
string strSampleFileName = "GRSRReportSample.xls";
string sSaveFileName = "GRSRReport.xls";
string sFullPath = Server.MapPath("/tmpfiles/GRSR/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
worksheet1.Rows[2].Cells[1].Value = LabelStandardTotalQty.Text;
worksheet1.Rows[2].Cells[3].Value = LabelStandardOneCount.Text;
worksheet1.Rows[2].Cells[4].Value = LabelStandardOneRatio.Text;
worksheet1.Rows[2].Cells[6].Value = LabelStandardTwoCount.Text;
worksheet1.Rows[2].Cells[7].Value = LabelStandardTwoRatio.Text;
worksheet1.Rows[2].Cells[9].Value = LabelStandardThreeCount.Text;
worksheet1.Rows[2].Cells[10].Value = LabelStandardThreeRatio.Text;
worksheet1.Rows[3].Cells[1].Value = LabelProductQtyTotalQty.Text;
worksheet1.Rows[3].Cells[3].Value = LabelProductQtyOneCount.Text;
worksheet1.Rows[3].Cells[4].Value = LabelProductQtyOneRatio.Text;
worksheet1.Rows[3].Cells[9].Value = LabelProductQtyThreeCount.Text;
worksheet1.Rows[3].Cells[10].Value = LabelProductQtyThreeRatio.Text;
worksheet1.Rows[4].Cells[1].Value = LabelDeliveryTotal.Text;
worksheet1.Rows[4].Cells[3].Value = LabelDeliveryOneCount.Text;
worksheet1.Rows[4].Cells[4].Value = LabelDeliveryOneRatio.Text;
worksheet1.Rows[4].Cells[6].Value = LabelDeliveryTwoCount.Text;
worksheet1.Rows[4].Cells[7].Value = LabelDeliveryTwoRatio.Text;
worksheet1.Rows[4].Cells[9].Value = LabelDeliveryThreeCount.Text;
worksheet1.Rows[4].Cells[10].Value = LabelDeliveryThreeRatio.Text;
worksheet1.Rows[5].Cells[1].Value = LabelServiceTotal.Text;
worksheet1.Rows[5].Cells[3].Value = LabelServiceOneCount.Text;
worksheet1.Rows[5].Cells[4].Value = LabelServiceOneRatio.Text;
worksheet1.Rows[5].Cells[6].Value = LabelServiceTwoCount.Text;
worksheet1.Rows[5].Cells[7].Value = LabelServiceTwoRatio.Text;
worksheet1.Rows[5].Cells[9].Value = LabelServiceThreeCount.Text;
worksheet1.Rows[5].Cells[10].Value = LabelServiceThreeRatio.Text;
worksheet1.Rows[6].Cells[1].Value = LabelRespondCount.Text;
worksheet1.Rows[6].Cells[3].Value = LabelRespondOneCount.Text;
worksheet1.Rows[6].Cells[4].Value = LabelRespondOneRatio.Text;
worksheet1.Rows[6].Cells[6].Value = LabelRespondTwoCount.Text;
worksheet1.Rows[6].Cells[7].Value = LabelRespondTwoRatio.Text;
worksheet1.Rows[6].Cells[9].Value = LabelRespondThreeCount.Text;
worksheet1.Rows[6].Cells[10].Value = LabelRespondThreeRatio.Text;
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
void CreateExcel(DataTable dt,DataTable itemcodeDt)
{
string strSampleFileName = "PaperBagReportSample.xls";
string sSaveFileName = "PaperBagReport.xls";
string sFullPath = Server.MapPath("/tmpfiles/PaperBag/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
int iCount = dt.Rows.Count;
int iInsertCount = iCount - 2;
//PaperBagReport
string from = this.CADateTimeFrom.IsDateEmpty ? string.Empty : this.CADateTimeFrom.IsDateEmpty ? string.Empty : this.CADateTimeFrom.SelectedDate.ToString("yyyy-MM-dd");
string to = this.CADateTimeTo.IsDateEmpty ? string.Empty : this.CADateTimeTo.IsDateEmpty ? string.Empty : this.CADateTimeTo.SelectedDate.ToString("yyyy-MM-dd");
worksheet1.Rows[0].Cells[0].Value = string.Format("Paper Bag Report-Items Summary ({0} - {1})", from, to);
worksheet1.Rows[2].InsertCopy(iInsertCount, worksheet1.Rows[2]);
for (int i = 0; i < iCount; i++)
{
worksheet1.Rows[i + 2].Cells[0].Value = dt.Rows[i]["ItemCode"];
worksheet1.Rows[i + 2].Cells[1].Value = dt.Rows[i]["Description"];
worksheet1.Rows[i + 2].Cells[2].Value = dt.Rows[i]["TotalQuantity"];
worksheet1.Rows[i + 2].Cells[3].Value = dt.Rows[i]["PackagedUnite"];
worksheet1.Rows[i + 2].Cells[4].Value = dt.Rows[i]["PackagedRegulation"];
worksheet1.Rows[i + 2].Cells[5].Value = dt.Rows[i]["Carton"];
worksheet1.Rows[i + 2].Cells[6].Value = dt.Rows[i]["Unit"];
worksheet1.Rows[i + 2].Cells[7].Value = dt.Rows[i]["CostCenterName"];
worksheet1.Rows[i + 2].Cells[8].Value = dt.Rows[i]["CostCenter"];
}
#region 纸袋统计数据
int iItemCount = itemcodeDt.Rows.Count;
int iItemCodeIndex=8 + iInsertCount;
worksheet1.Rows[iItemCodeIndex].InsertCopy(iItemCount - 2, worksheet1.Rows[iItemCodeIndex]);
for (int i = 0; i < iItemCount; i++)
{
worksheet1.Rows[i+iItemCodeIndex].Cells[0].Value=itemcodeDt.Rows[i]["ItemCode"];
worksheet1.Rows[i + iItemCodeIndex].Cells[1].Value = itemcodeDt.Rows[i]["Description"];
worksheet1.Rows[i + iItemCodeIndex].Cells[2].Value = itemcodeDt.Rows[i]["TotalQuantity"];
worksheet1.Rows[i + iItemCodeIndex].Cells[3].Value = itemcodeDt.Rows[i]["PackagedUnite"];
worksheet1.Rows[i + iItemCodeIndex].Cells[4].Value = itemcodeDt.Rows[i]["PackagedRegulation"];
worksheet1.Rows[i + iItemCodeIndex].Cells[5].Value = itemcodeDt.Rows[i]["Carton"];
worksheet1.Rows[i + iItemCodeIndex].Cells[6].Value = itemcodeDt.Rows[i]["Unit"];
}
#endregion
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
