// 导出Excel
private void btn_ExportExcel_Click(object sender, EventArgs e)
{
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("无数据", "提示", MessageBoxButtons.OK);
return;
}
string productIDs = "(";
List <Model.Product> list = new List <Book.Model.Product>();
//DataTable excelDT = new DataTable();
//excelDT.Columns.Add("ProductId", typeof(string));
//excelDT.Columns.Add("Id", typeof(string));
//excelDT.Columns.Add("ProductName", typeof(string));
//excelDT.Columns.Add("Quantity", typeof(string));
//excelDT.Columns.Add("ProductCategoryName1", typeof(string));
//excelDT.Columns.Add("ProductCategoryName2", typeof(string));
//excelDT.Columns.Add("ProductCategoryName3", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0 || dt.Rows[i]["productid"].ToString() != dt.Rows[i - 1]["productid"].ToString())
{
//excelDT.Rows.Add(dt.Rows[i]["productid"].ToString(), dt.Rows[i]["spid"].ToString(), dt.Rows[i]["productName"].ToString(), dt.Rows[i]["Quantity"].ToString());
list.Add(new Model.Product()
{
ProductId = dt.Rows[i]["productid"].ToString(), Id = dt.Rows[i]["spid"].ToString(), ProductName = dt.Rows[i]["productName"].ToString(), StocksQuantity = Convert.ToDouble(dt.Rows[i]["Quantity"]), CnName = dt.Rows[i]["CnName"].ToString(), NetWeight = Convert.ToDouble(dt.Rows[i]["NetWeight"])
});
productIDs += "'" + dt.Rows[i]["productid"].ToString() + "',";
}
else
{
//excelDT.Rows[excelDT.Rows.Count - 1]["Quantity"] = Convert.ToDouble(excelDT.Rows[excelDT.Rows.Count - 1]["Quantity"]) + Convert.ToDouble(dt.Rows[i]["Quantity"]);
Model.Product pro = list.First(P => P.ProductId == dt.Rows[i]["productid"].ToString());
pro.StocksQuantity = Convert.ToDouble(pro.StocksQuantity) + Convert.ToDouble(dt.Rows[i]["Quantity"]);
}
}
productIDs = productIDs.TrimEnd(',') + ")";
DataTable dtCategory = productManager.SelectProductCategoryByProductIds(productIDs);
//excelDT.AsEnumerable().ToList().ForEach(P =>
//{
// DataRow dr = dtCategory.AsEnumerable().First(D => D.Field<string>("ProductId") == P.Field<string>("productid"));
// P["ProductCategoryName1"] = dr["ProductCategoryName1"];
// P["ProductCategoryName2"] = dr["ProductCategoryName2"];
// P["ProductCategoryName3"] = dr["ProductCategoryName3"];
//});
list.ForEach(Product =>
{
DataRow dr = dtCategory.AsEnumerable().First(D => D.Field <string>("ProductId") == Product.ProductId);
Product.ProductCategoryName = dr["ProductCategoryName1"].ToString();
Product.ProductCategoryName2 = dr["ProductCategoryName2"].ToString();
Product.ProductCategoryName3 = dr["ProductCategoryName3"].ToString();
Product.MaterialIds = dr["MaterialIds"].ToString();
Product.MaterialNum = dr["MaterialNum"].ToString();
});
//DataRow[] dtHaveThreeCategory = excelDT.Select("ProductCategoryName3 is not null");
//DataRow[] dtHaveTwoCategory = excelDT.Select("ProductCategoryName3 is null and ProductCategoryName2 is not null");
//DataRow[] dtHaveOneCategory = excelDT.Select("ProductCategoryName2 is null and ProductCategoryName3 is null");
List <Model.Product> listHaveThreeCategory = list.Where(P => !string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
List <Model.Product> listHaveTwoCategory = list.Where(P => !string.IsNullOrEmpty(P.ProductCategoryName2) && string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
List <Model.Product> listHaveOneCategory = list.Where(P => string.IsNullOrEmpty(P.ProductCategoryName2) && string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
CommonHelp.ConvertMaterial(list);
try
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]);
r.MergeCells = true;//合并单元格
excel.Cells.ColumnWidth = 20;
excel.Cells[1, 1] = "商品即时库存(" + this.dateEdit1.DateTime.ToString("yyyy-MM-dd") + ")";
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20;
excel.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).HorizontalAlignment = -4108;
excel.Cells[2, 1] = "商品编号";
excel.Cells[2, 2] = "商品名称";
excel.Cells[2, 3] = "即时库存";
excel.Cells[2, 4] = "单位";
excel.Cells[2, 5] = "单个商品净重";
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 4]).Interior.Color = "12566463";
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 2]).ColumnWidth = 50;
int row = 3;
int col = 6;
//原料
foreach (var item in list[0].MaterialDic)
{
excel.Cells[2, col++] = item.Key;
}
foreach (var item in listHaveThreeCategory.GroupBy(P => P.ProductCategoryName3))
{
SetExcelFormat(excel, ref row, item);
}
foreach (var item in listHaveTwoCategory.GroupBy(P => P.ProductCategoryName2))
{
SetExcelFormat(excel, ref row, item);
}
foreach (var item in listHaveOneCategory.GroupBy(P => P.ProductCategoryName))
{
SetExcelFormat(excel, ref row, item);
}
excel.Visible = true;//是否打开该Excel文件
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
}
catch
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}
//导出Excel
private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (productList == null || productList.Count == 0)
{
MessageBox.Show("无数据", "提示", MessageBoxButtons.OK);
return;
}
try
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
//按商品类别级别分类
List <Model.Product> listHaveThreeCategory = productList.Where(P => !string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
List <Model.Product> listHaveTwoCategory = productList.Where(P => !string.IsNullOrEmpty(P.ProductCategoryName2) && string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
List <Model.Product> listHaveOneCategory = productList.Where(P => string.IsNullOrEmpty(P.ProductCategoryName2) && string.IsNullOrEmpty(P.ProductCategoryName3)).ToList();
//换算原料
CommonHelp.ConvertMaterial(productList);
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 7]);
r.MergeCells = true;//合并单元格
excel.Cells.ColumnWidth = 20;
excel.Cells[1, 1] = "商品即时库存手册版(" + this.dateEdit1.DateTime.ToString("yyyy-MM-dd") + ")";
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 9]).HorizontalAlignment = -4108;
excel.get_Range(excel.Cells[2, 2], excel.Cells[2, 3]).ColumnWidth = 50;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 9]).Interior.Color = "12566463";
excel.Cells[2, 1] = "库房";
excel.Cells[2, 2] = "商品编号";
excel.Cells[2, 3] = "商品名称";
excel.Cells[2, 4] = "即时库存";
excel.Cells[2, 5] = "单位";
excel.Cells[2, 6] = "手册号";
excel.Cells[2, 7] = "手册项号";
excel.Cells[2, 8] = "客户订单号";
excel.Cells[2, 9] = "单个商品净重";
int row = 3;
int col = 10;
//原料
foreach (var item in productList[0].MaterialDic)
{
excel.Cells[2, col++] = item.Key;
}
foreach (var item in listHaveThreeCategory.GroupBy(P => P.ProductCategoryName3))
{
SetExcelFormat(excel, ref row, item);
}
foreach (var item in listHaveTwoCategory.GroupBy(P => P.ProductCategoryName2))
{
SetExcelFormat(excel, ref row, item);
}
foreach (var item in listHaveOneCategory.GroupBy(P => P.ProductCategoryName))
{
SetExcelFormat(excel, ref row, item);
}
excel.Visible = true;//是否打开该Excel文件
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
}
catch
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}