protected void Test()
{
XlsDocument xls = new XlsDocument();
xls.FileName = "D:\\12333333.xls";
string sheetName = "chc 实例";
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格（cell）集合
//单元格1-base
Cell cell = cells.Add(1, 2, "抗"); //设定第一行，第二例单元格的值
cell.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cell.Font.FontName = "方正舒体"; //设定字体
cell.Font.Height = 20 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cell.UseBorder = true; //使用边框
cell.BottomLineStyle = 2; //设定边框底线为粗线
cell.BottomLineColor = Colors.DarkRed; //设定颜色为暗红
//cell的格式还可以定义在一个xf对象中
CellFormat cellXF = xls.NewXF(); //为xls生成一个XF实例（XF是cell格式对象）
cellXF.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXF.Font.FontName = "方正舒体"; //设定字体
cellXF.Font.Height = 20 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXF.UseBorder = true; //使用边框
cellXF.BottomLineStyle = 2; //设定边框底线为粗线
cellXF.BottomLineColor = Colors.DarkRed; //设定颜色为暗红
cell = cells.Add(2, 2, "震", cellXF); //以设定好的格式填加cell
cellXF.Font.FontName = "仿宋_GB2312";
cell = cells.Add(3, 2, "救", cellXF); //格式可以多次使用
ColumnInfo colInfo = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = 1; //起始列为第二列
colInfo.ColumnIndexEnd = 5; //终止列为第六列
colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
colInfo.ColumnIndexEnd = 6; //可以更改列对象的值
ColumnInfo colInfo2 = new ColumnInfo(xls, sheet); //通过新生成一个列格式对象，才到能设定其它列宽度
colInfo2.ColumnIndexStart = 7;
colInfo2.ColumnIndexEnd = 8;
colInfo2.Width = 1 * 256;
sheet.AddColumnInfo(colInfo2);
MergeArea meaA = new MergeArea(1, 2, 3, 4); //一个合并单元格实例(合并第一行、第三例 到 第二行、第四例)
sheet.AddMergeArea(meaA); //填加合并单元格
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.Font.Height = 48 * 20;
cellXF.Font.Bold = true;
cellXF.Pattern = 3; //设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternBackgroundColor = Colors.DarkRed; //填充的底色
cellXF.PatternColor = Colors.DarkGreen; //设定填充线条的颜色
cell = cells.Add(1, 3, "灾", cellXF);
xls.Save();
}
/// <summary>
/// 添加单元格数据
/// </summary>
/// <param name="data">数据值</param>
/// <param name="mergeInfo">单元格合并信息</param>
private void AddCellData(string data)
{
if (WithinMergeArea(rowIndex, colIndex))
{
colIndex++;
AddCellData(data);
}
else
{
MergeInfo mergeInfo = GetMergeInfo(data);
SetCellStyleByCellData(data);
data = Regex.Replace(data, @"\(\?!.+?\)", "");
double dataDou;
if (double.TryParse(data, out dataDou))
{
Cell cell = worksheet.Cells.Add(rowIndex, colIndex, dataDou, cellStyle);
}
else
{
worksheet.Cells.Add(rowIndex, colIndex, data, cellStyle);
}
if (mergeInfo != null)
{
MergeArea mergeArea;
if (string.Equals(mergeInfo.MergeType, "colspan", StringComparison.InvariantCultureIgnoreCase))
{
mergeArea = new MergeArea()
{
RowMin = (ushort)rowIndex, RowMax = (ushort)rowIndex, ColMin = (ushort)colIndex, ColMax = (ushort)(colIndex + mergeInfo.MergeSpan - 1)
};
}
else
{
mergeArea = new MergeArea()
{
RowMin = (ushort)rowIndex, RowMax = (ushort)(rowIndex + mergeInfo.MergeSpan - 1), ColMin = (ushort)colIndex, ColMax = (ushort)colIndex
};
}
worksheet.AddMergeArea(mergeArea);
mergeAreaList.Add(mergeArea);
}
}
}
protected void ExcelOut(string CateId, string TestName, int Num, int Type)
{
//生成Excel开始
string FilePath = "~/xml/Demo.xls";
string NewTestName = "Sheet2";
if (Type == 2)
{
NewTestName = TestName + "答案";
}
XlsDocument xls = new XlsDocument(); //创建空xls文档
xls.FileName = Server.MapPath(FilePath); //保存路径，如果直接发送到客户端的话只需要名称 生成名称
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(TestName); //创建一个工作页为Dome
Worksheet sheet2 = xls.Workbook.Worksheets.AddNamed(NewTestName);
//设置文档列属性
//ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
//cinfo.Collapsed = true;
////设置列的范围 如 0列-10列
//cinfo.ColumnIndexStart = 0;//列开始
//cinfo.ColumnIndexEnd = 10;//列结束
//cinfo.Collapsed = true;
//cinfo.Width = 90 * 60;//列宽度
//sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, 2);//从第1行跨到第二行，从第一列跨到第5列
sheet.AddMergeArea(ma);
ma = new MergeArea(1, 1, 1, 3);
sheet2.AddMergeArea(ma);
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
//cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
//cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
//cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
Cells cells2 = sheet2.Cells; //获得指定工作页列集合
//列操作基本
//Cell cell = cells.Add(1, 1, TestName, cellXF);//添加标题列返回一个列 参数：行 列 名称 样式对象
Cell cell = cells.Add(1, 1, TestName, cellXF);
if (Type == 2)
{
cells2.Add(1, 1, NewTestName);
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true; //设置粗体
cell.Font.ColorIndex = 0; //设置颜色码
cell.Font.FontFamily = FontFamilies.Roman; //设置字体 默认为宋体
//创建列结束
//创建数据
QuestionInfo QuestionModel = new QuestionInfo();
QuestionModel.IdCondition = CateId;
if (Num != 0)
{
QuestionModel.QuestionNum = " Top " + Num.ToString() + " * ";
}
List <QuestionInfo> QuestionList = QuestionBLL.ReadList(QuestionModel);
int LineNum = 1;
int LineNum2 = 1;
//题型的题号
int StyleNum = 1;
//题型号转为中文
string StyleCNNum = string.Empty;
//题型的中文名称
string StyleName = string.Empty;
for (int j = 1; j <= 3; j++)
{
List <QuestionInfo> StyleList = QuestionList.FindAll(delegate(QuestionInfo TempModel) { return(TempModel.Style == j.ToString()); });
if (StyleList.Count > 0)
{
switch (StyleNum)
{
case 1:
StyleCNNum = "一";
break;
case 2:
StyleCNNum = "二";
break;
case 3:
StyleCNNum = "三";
break;
}
switch (j)
{
case 1:
StyleName = "单项选择题";
break;
case 2:
StyleName = "多项选择题";
break;
case 3:
StyleName = "判断题";
break;
}
if (Type == 2)
{
cells2.Add(LineNum2 + 1, 1, StyleCNNum + "、");
cells2.Add(LineNum2 + 1, 2, StyleName);
LineNum2 = LineNum2 + 2;
}
cells.Add(LineNum + 1, 1, StyleCNNum + "、");
cells.Add(LineNum + 1, 2, StyleName);
StyleNum++;
LineNum = LineNum + 2;
for (int i = 0; i < StyleList.Count; i++)
{
cells.Add(LineNum + 1, 1, i + 1 + "、");//添加列，不设置列属性就不用返回列对象
if (Type == 2)
{
cells2.Add(LineNum2 + 1, 2, i + 1 + "、");
}
if (Type == 1)
{
cells.Add(LineNum + 1, 2, StyleList[i].Question);//添加列，不设置列属性就不用返回列对象
if (j != 3)
{
cells.Add(LineNum + 2, 1, "A、");
cells.Add(LineNum + 2, 2, StyleList[i].A);
cells.Add(LineNum + 3, 1, "B、");
cells.Add(LineNum + 3, 2, StyleList[i].B);
cells.Add(LineNum + 4, 1, "C、");
cells.Add(LineNum + 4, 2, StyleList[i].C);
cells.Add(LineNum + 5, 1, "D、");
cells.Add(LineNum + 5, 2, StyleList[i].D);
cells.Add(LineNum + 6, 1, "答案：");
cells.Add(LineNum + 6, 2, StyleList[i].Answer.ToUpper());
LineNum = LineNum + 7;
}
else
{
cells.Add(LineNum + 2, 1, "改错：");
cells.Add(LineNum + 2, 2, StyleList[i].A);
LineNum = LineNum + 3;
}
}
else if (Type == 2)
{
cells.Add(LineNum + 1, 2, StyleList[i].Question.Replace("~", ""));//添加列，不设置列属性就不用返回列对象
if (j != 3)
{
cells.Add(LineNum + 2, 2, "A、" + StyleList[i].A);
cells.Add(LineNum + 3, 2, "B、" + StyleList[i].B);
cells.Add(LineNum + 4, 2, "C、" + StyleList[i].C);
cells.Add(LineNum + 5, 2, "D、" + StyleList[i].D);
LineNum = LineNum + 6;
cells2.Add(LineNum2 + 1, 3, StyleList[i].Answer);
}
else
{
cells.Add(LineNum + 2, 2, "改错：");
LineNum = LineNum + 3;
if (StyleList[i].Answer == "0")
{
cells2.Add(LineNum2 + 1, 3, StyleList[i].A);
}
else
{
cells2.Add(LineNum2 + 1, 3, "√");
}
}
LineNum2 = LineNum2 + 1;
}
}
}
}
//
//生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
//ScriptHelper.Alert(Server.MapPath("~/Demo.xls"));
if (File.Exists(Server.MapPath(FilePath)))
{
File.Delete(Server.MapPath(FilePath));//删除
}
//保存文档
xls.Save(Server.MapPath(FilePath)); //保存到服务器
xls.Send(); //发送到客户端
}
protected void ExcelOut(XlsDocument xls, string CateId, string TestName, int Num, int Type)
{
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(TestName); //创建一个工作页为Dome
//设置文档列属性
//ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
//cinfo.Collapsed = true;
////设置列的范围 如 0列-10列
//cinfo.ColumnIndexStart = 0;//列开始
//cinfo.ColumnIndexEnd = 10;//列结束
//cinfo.Collapsed = true;
//cinfo.Width = 90 * 60;//列宽度
//sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, 2);//从第1行跨到第二行，从第一列跨到第5列
sheet.AddMergeArea(ma);
ma = new MergeArea(1, 1, 1, 3);
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
//cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
//cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
//cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//列操作基本
//Cell cell = cells.Add(1, 1, TestName, cellXF);//添加标题列返回一个列 参数：行 列 名称 样式对象
Cell cell = cells.Add(1, 1, TestName, cellXF);
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true; //设置粗体
cell.Font.ColorIndex = 0; //设置颜色码
cell.Font.FontFamily = FontFamilies.Roman; //设置字体 默认为宋体
//创建列结束
//创建数据
QuestionInfo QuestionModel = new QuestionInfo();
QuestionModel.IdCondition = CateId;
if (Num != 0)
{
QuestionModel.QuestionNum = " Top " + Num.ToString() + " * ";
}
List <QuestionInfo> QuestionList = QuestionBLL.ReadList(QuestionModel);
int LineNum = 1;
int LineNum2 = 1;
//题型的题号
int StyleNum = 1;
//题型号转为中文
string StyleCNNum = string.Empty;
//题型的中文名称
string StyleName = string.Empty;
for (int j = 1; j <= 3; j++)
{
List <QuestionInfo> StyleList = QuestionList.FindAll(delegate(QuestionInfo TempModel) { return(TempModel.Style == j.ToString()); });
if (StyleList.Count > 0)
{
switch (StyleNum)
{
case 1:
StyleCNNum = "一";
break;
case 2:
StyleCNNum = "二";
break;
case 3:
StyleCNNum = "三";
break;
}
switch (j)
{
case 1:
StyleName = "单项选择题";
break;
case 2:
StyleName = "多项选择题";
break;
case 3:
StyleName = "判断题";
break;
}
if (Type == 2)
{
LineNum2 = LineNum2 + 2;
}
cells.Add(LineNum + 1, 1, StyleCNNum + "、");
cells.Add(LineNum + 1, 2, StyleName);
StyleNum++;
LineNum = LineNum + 2;
for (int i = 0; i < StyleList.Count; i++)
{
cells.Add(LineNum + 1, 1, i + 1 + "、");//添加列，不设置列属性就不用返回列对象
if (Type == 1)
{
cells.Add(LineNum + 1, 2, StyleList[i].Question);//添加列，不设置列属性就不用返回列对象
if (j != 3)
{
cells.Add(LineNum + 2, 1, "A、");
cells.Add(LineNum + 2, 2, StyleList[i].A);
cells.Add(LineNum + 3, 1, "B、");
cells.Add(LineNum + 3, 2, StyleList[i].B);
cells.Add(LineNum + 4, 1, "C、");
cells.Add(LineNum + 4, 2, StyleList[i].C);
cells.Add(LineNum + 5, 1, "D、");
cells.Add(LineNum + 5, 2, StyleList[i].D);
cells.Add(LineNum + 6, 1, "答案：");
cells.Add(LineNum + 6, 2, StyleList[i].Answer.ToUpper());
LineNum = LineNum + 7;
}
else
{
cells.Add(LineNum + 2, 1, "改错：");
cells.Add(LineNum + 2, 2, StyleList[i].A);
LineNum = LineNum + 3;
}
}
}
}
}
}
/// <summary>
/// 利用MyXls控件的一个方法，导出到电子表格
/// </summary>
/// <param name="dsxls">数据集，列明应该是中文滴</param>
/// <param name="filename">导出文件名</param>
/// <param name="sheetsname">导出的sheet名</param>
/// <param name="title">大标题，如果留空，则不显示大标题</param>
/// <param name="rowwidth">每个列的宽度</param>
/// <param name="lujing">保存路径</param>
public static void goxls(DataSet dsxls, string filename, string sheetsname, string title, int rowwidth, string lujing)
{
org.in2bits.MyXls.XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add(sheetsname);
Cells cells = sheet.Cells;
ColumnInfo colInfo = new ColumnInfo(doc, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = (ushort)(dsxls.Tables[0].Columns.Count - 1);
colInfo.Width = (ushort)(rowwidth * 256);
sheet.AddColumnInfo(colInfo);
XF xf_col = doc.NewXF();
xf_col.HorizontalAlignment = HorizontalAlignments.Centered;
xf_col.VerticalAlignment = VerticalAlignments.Centered;
xf_col.Pattern = 1;
xf_col.PatternColor = Colors.Default30;
xf_col.UseBorder = true;
xf_col.TopLineStyle = 1;
xf_col.TopLineColor = Colors.Black;
xf_col.BottomLineStyle = 1;
xf_col.BottomLineColor = Colors.Black;
xf_col.LeftLineStyle = 1;
xf_col.LeftLineColor = Colors.Black;
xf_col.RightLineStyle = 1;
xf_col.RightLineColor = Colors.Black;
xf_col.Font.Bold = true;
xf_col.Font.Height = 11 * 20;
xf_col.Font.ColorIndex = 1;
int rowCount = dsxls.Tables[0].Rows.Count;
int colCount = dsxls.Tables[0].Columns.Count;
int rowMin = 1, colMin = 1;
int rowbegin = 0;
if (title.Trim() != "")
{
MergeArea meaA = new MergeArea(1, 1, 1, dsxls.Tables[0].Columns.Count);
sheet.AddMergeArea(meaA);//填加合并单元格
Cell cell_title = cells.Add(1, 1, title);
cell_title.Font.Height = 15 * 20;
rowbegin = 1;
}
for (int row = rowbegin; row < rowCount + rowbegin + 1; row++)
{
if (row == rowbegin)
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[0].Columns[col - 1].ColumnName, xf_col);
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[0].Rows[row - 1 - rowbegin][col - 1].ToString());
}
}
}
try
{
doc.Save(lujing, true);
}
catch (Exception ex)
{
;
}
//doc.Send();
//Response.Flush();
//Response.End();
}
/// <summary>
/// 利用MyXls控件的一个方法，导出到电子表格
/// </summary>
/// <param name="dsxls">数据集，列明应该是中文滴</param>
/// <param name="filename">导出文件名</param>
/// <param name="sheetsname">导出的sheet名</param>
/// <param name="title">大标题列表，根据dt中表格的顺序添加</param>
/// <param name="title2">大标题2</param>
/// <param name="rowwidth">每个列的宽度</param>
/// <param name="lujing">保存路径</param>
public static void goxls_3(DataSet dsxls, string filename, string sheetsname, ArrayList title, string title2, int rowwidth, string lujing)
{
org.in2bits.MyXls.XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add(sheetsname);
Cells cells = sheet.Cells;
ColumnInfo colInfo = new ColumnInfo(doc, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = (ushort)(dsxls.Tables[0].Columns.Count - 1);
colInfo.Width = (ushort)(rowwidth * 256);
sheet.AddColumnInfo(colInfo);
XF xf_col = doc.NewXF();
xf_col.HorizontalAlignment = HorizontalAlignments.Centered;
xf_col.VerticalAlignment = VerticalAlignments.Centered;
xf_col.Pattern = 1;
xf_col.PatternColor = Colors.Grey;
xf_col.UseBorder = true;
xf_col.TopLineStyle = 1;
xf_col.TopLineColor = Colors.Black;
xf_col.BottomLineStyle = 1;
xf_col.BottomLineColor = Colors.Black;
xf_col.LeftLineStyle = 1;
xf_col.LeftLineColor = Colors.Black;
xf_col.RightLineStyle = 1;
xf_col.RightLineColor = Colors.Black;
xf_col.Font.Bold = true;
xf_col.Font.Height = 10 * 20;
xf_col.Font.ColorIndex = 1;
int totalrow = 0;
for (int i = 0; i < dsxls.Tables.Count; i++)
{
int rowCount = dsxls.Tables[i].Rows.Count;
int colCount = dsxls.Tables[i].Columns.Count;
totalrow = totalrow + dsxls.Tables[i].Rows.Count;
int rowMin = i > 0 ? totalrow - rowCount + (i * 3) + 1 : 1;
int colMin = 1;
int rowtitle = 0;
string title1 = title[i].ToString();
if (title1.Trim() != "" && title2.Trim() != "")
{
MergeArea meaA1 = new MergeArea(rowMin, rowMin, 1, dsxls.Tables[i].Columns.Count);
sheet.AddMergeArea(meaA1);//填加合并单元格
Cell cell_title1 = cells.Add(rowMin, 1, title[i]);
cell_title1.Font.Height = 12 * 18;
cell_title1.HorizontalAlignment = HorizontalAlignments.Centered;
cell_title1.Font.Bold = true;
MergeArea meaA2 = new MergeArea(rowMin + 1, rowMin + 1, 1, dsxls.Tables[i].Columns.Count);
sheet.AddMergeArea(meaA2);//填加合并单元格
Cell cell_title2 = cells.Add(rowMin + 1, 1, title2);
cell_title2.Font.Height = 10 * 20;
rowtitle = 2;
}
for (int row = rowtitle; row < rowCount + rowtitle + 1; row++)
{
if (row == rowtitle)
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[i].Columns[col - 1].ColumnName, xf_col);
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, dsxls.Tables[i].Rows[row - 1 - rowtitle][col - 1].ToString());
}
}
}
}
doc.Save(lujing, true);
//doc.Send();
//Response.Flush();
//Response.End();
}
public void ProcessRequest(HttpContext context)
{
//导表智能中心订单进度表
List <OrderInfoSchedu> orderInfoSchedus = new List <OrderInfoSchedu>();
int mounth = DateTime.Now.Month;
int year = DateTime.Now.Year;
using (JDJS_WMS_DB_USEREntities wms = new JDJS_WMS_DB_USEREntities())
{
var orders = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Intention == 2 || r.Intention == 3 || r.Intention == 4);
foreach (var item in orders)
{
int orderID = Convert.ToInt32(item.Order_ID);
if (item.Intention == 4)
{
var overProcess = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag != 0);
var endTime = overProcess.OrderByDescending(r => r.EndTime).FirstOrDefault();
if (endTime != null)
{
var time = Convert.ToDateTime(endTime.EndTime).Month;
var yeartime = Convert.ToDateTime(endTime.EndTime).Year;
if (time != mounth && year != yeartime)
{
break;
}
}
}
OrderInfoSchedu orderInfoSchedu = new OrderInfoSchedu();
var orderInf0 = wms.JDJS_WMS_Order_Guide_Schedu_Table.Where(r => r.OrderID == orderID).FirstOrDefault();
if (orderInf0 != null)
{
orderInfoSchedu.Client = orderInf0.ClientName;
if (orderInf0.EndTime != null)
{
orderInfoSchedu.EndTime = Convert.ToDateTime(orderInf0.EndTime).Date.ToShortDateString();
}
else
{
orderInfoSchedu.EndTime = "/";
}
if (orderInf0.ExpectEndTime != null)
{
orderInfoSchedu.ExpectEndTime = Convert.ToDateTime(orderInf0.ExpectEndTime).Date.ToShortDateString();
}
else
{
orderInfoSchedu.ExpectEndTime = "/";
}
if (orderInf0.FileDownTime != null)
{
orderInfoSchedu.FileDownTime = Convert.ToDateTime(orderInf0.FileDownTime).Date.ToShortDateString();
}
else
{
orderInfoSchedu.FileDownTime = "/";
}
}
orderInfoSchedu.Name = item.Product_Name;
orderInfoSchedu.OrderNum = item.Order_Number;
orderInfoSchedu.EngineerName = item.Engine_Program_Manager;
var processes = wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.OrderID == orderID && r.sign != 0);
orderInfoSchedu.jiawei = processes.Count().ToString();
var schedu1 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 1);
var schedu2 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 2);
var schedu3 = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.OrderID == orderID && r.isFlag == 3);
if (schedu1.Count() > 0 && schedu2.Count() < 1 && schedu3.Count() < 1)
{
orderInfoSchedu.State = "等待生产中";
var blankInfo = wms.JDJS_WMS_Blank_Table.Where(r => r.OrderID == orderID);
if (blankInfo.Count() < 1)
{
orderInfoSchedu.State = "待料";
}
var toolInfo = wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.OrderID == orderID && r.sign != 0 && r.toolPreparation == 1);
if (toolInfo.Count() < 1)
{
orderInfoSchedu.State = "待备刀装刀";
}
}
if (schedu1.Count() < 1 && schedu2.Count() < 1 && schedu3.Count() < 1)
{
orderInfoSchedu.State = "编程中";
}
if ((schedu2.Count() > 0))
{
orderInfoSchedu.State = "生产中";
}
if (schedu1.Count() > 0 && schedu3.Count() > 0)
{
orderInfoSchedu.State = "生产中";
}
if (schedu1.Count() < 1 && schedu2.Count() < 1 && schedu3.Count() > 0)
{
orderInfoSchedu.State = "已完成";
}
orderInfoSchedus.Add(orderInfoSchedu);
}
}
XlsDocument doc = new XlsDocument();
doc.FileName = DateTime.Now.ToString("yyyyMMddhhmmssms") + "订单进度" + ".xls";
Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
Cells cells = sheet.Cells;
ColumnInfo col = new ColumnInfo(doc, sheet); //创建列样式对象
col.ColumnIndexStart = 2; //起始列，索引从0开始
col.ColumnIndexEnd = 2; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col.Width = 8888; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col); //将列样式作用于此工作表
ColumnInfo col2 = new ColumnInfo(doc, sheet); //创建列样式对象
col2.ColumnIndexStart = 3; //起始列，索引从0开始
col2.ColumnIndexEnd = 3; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col2.Width = 3333; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col2); //将列样式作用于此工作表
ColumnInfo col6 = new ColumnInfo(doc, sheet); //创建列样式对象
col6.ColumnIndexStart = 6; //起始列，索引从0开始
col6.ColumnIndexEnd = 6; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col6.Width = 5555; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col6); //将列样式作用于此工作表
ColumnInfo col7 = new ColumnInfo(doc, sheet); //创建列样式对象
col7.ColumnIndexStart = 7; //起始列，索引从0开始
col7.ColumnIndexEnd = 7; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col7.Width = 5555; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col7); //将列样式作用于此工作表
ColumnInfo col8 = new ColumnInfo(doc, sheet); //创建列样式对象
col8.ColumnIndexStart = 8; //起始列，索引从0开始
col8.ColumnIndexEnd = 8; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col8.Width = 5555; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col8); //将列样式作用于此工作表
MergeArea ma1 = new MergeArea(1, 1, 1, 10); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma1); //添加合并单元格到工作表
MergeArea ma2 = new MergeArea(2, 2, 2, 9); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma2); //添加合并单元格到工作表
XF xf = doc.NewXF(); //单元格样式对象
xf.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf.PatternColor = Colors.Green; //填充背景底色
xf.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf.Font.FontName = "黑体"; //字体
xf.Font.Height = 15 * 15; //字体大小
//xf.UseBorder = false ; //使用边框
//xf.BottomLineStyle = 1; //边框样式
//xf.BottomLineColor = Colors.Black; //边框颜色
XF xf1 = doc.NewXF(); //单元格样式对象
xf1.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf1.HorizontalAlignment = HorizontalAlignments.Right; //水平居中
xf1.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf1.PatternColor = Colors.Green; //填充背景底色
xf1.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf1.Font.FontName = "宋体"; //字体
xf1.Font.Height = 13 * 13; //字体大小
//xf1.UseBorder = false ; //使用边框
//xf1.BottomLineStyle = 1; //边框样式
//xf1.BottomLineColor = Colors.Black; //边框颜色
XF xf2 = doc.NewXF(); //单元格样式对象
xf2.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf2.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf2.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf2.PatternColor = Colors.Green; //填充背景底色
xf2.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf2.Font.FontName = "宋体"; //字体
xf2.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
XF xf3 = doc.NewXF(); //单元格样式对象
xf3.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf3.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf3.Pattern = 1; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf3.PatternColor = Colors.Green; //填充背景底色
xf3.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf3.Font.FontName = "宋体"; //字体
xf3.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
var DATA = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月";
cells.Add(1, 1, DATA + "智能中心项目进度表", xf); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(2, 2, "日期：" + DateTime.Now.Date.ToShortDateString(), xf1); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(3, 1, "序号", xf2);
cells.Add(3, 2, "客户名称", xf2);
cells.Add(3, 3, "客户零件编号", xf2);
cells.Add(3, 4, "内部零件编号", xf2);
cells.Add(3, 5, "工程师", xf2);
cells.Add(3, 6, "夹位", xf2);
cells.Add(3, 7, "文件下发时间", xf2);
cells.Add(3, 8, "预计编程完成时间", xf2);
cells.Add(3, 9, "完成时间", xf2);
cells.Add(3, 10, "备注", xf2);
int index = 4;
for (int i = 0; i < orderInfoSchedus.Count(); i++)
{
cells.Add(i + 4, 1, (i + 1).ToString(), xf2);
cells.Add(i + 4, 2, orderInfoSchedus[i].Client, xf2);
cells.Add(i + 4, 3, orderInfoSchedus[i].Name, xf2);
cells.Add(i + 4, 4, orderInfoSchedus[i].OrderNum, xf2);
cells.Add(i + 4, 5, orderInfoSchedus[i].EngineerName, xf2);
cells.Add(i + 4, 6, orderInfoSchedus[i].jiawei, xf2);
cells.Add(i + 4, 7, orderInfoSchedus[i].FileDownTime, xf2);
cells.Add(i + 4, 8, orderInfoSchedus[i].ExpectEndTime, xf2);
cells.Add(i + 4, 9, orderInfoSchedus[i].EndTime, xf2);
cells.Add(i + 4, 10, orderInfoSchedus[i].State, xf2);
index++;
}
PathInfo pathInfo = new PathInfo();
string path = pathInfo.upLoadPath() + @"ExcelFile\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
doc.Save(path);
context.Response.Write(pathInfo.downLoadPath() + @"ExcelFile\" + doc.FileName);
}
protected void ExcelOut(List <UserInfo> userList)
{
//生成Excel开始
string FilePath = "~/xml/Demo.xls";
XlsDocument xls = new XlsDocument(); //创建空xls文档
xls.FileName = Server.MapPath(FilePath); //保存路径，如果直接发送到客户端的话只需要名称 生成名称
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("会员列表"); //创建一个工作页为Dome
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, 6);//合并单元格 行与列
sheet.AddMergeArea(ma);
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
//cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
//cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
//cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//列操作基本
Cell cell = cells.Add(1, 1, "会员列表", cellXF);
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true; //设置粗体
cell.Font.ColorIndex = 0; //设置颜色码
cell.Font.FontFamily = FontFamilies.Roman; //设置字体 默认为宋体
//创建列结束
//创建数据
int LineNum = 3;
if (userList.Count > 0)
{
cells.Add(2, 1, "序号");
cells.Add(2, 2, "姓名");
cells.Add(2, 3, "公司简称");
cells.Add(2, 4, "品牌");
cells.Add(2, 5, "手机");
cells.Add(2, 6, "Email");
//cells.Add(2, 7, "原始岗位");
//cells.Add(2, 5, "性别");
//cells.Add(2, 8, "用户ID");
//cells.Add(2, 9, "工作岗位");
//cells.Add(2, 9, "学习岗位");
//cells.Add(2, 10, "帐户类型");
foreach (UserInfo Info in userList)
{
cells.Add(LineNum, 1, LineNum - 2);
cells.Add(LineNum, 2, Info.RealName);
cells.Add(LineNum, 3, ReadCompany(Info.CompanyID).CompanySimpleName);
cells.Add(LineNum, 4, ReadBrandName(ProductBrandBLL.ReadProductBrandCacheList(StringHelper.SubString(ReadCompany(Info.CompanyID).BrandId, "17"))));
cells.Add(LineNum, 5, Info.Mobile);
cells.Add(LineNum, 6, Info.Email);
//cells.Add(LineNum, 7, Info.PostName);
//cells.Add(LineNum, 5, EnumHelper.ReadEnumChineseName<SexType>(Info.Sex));
//cells.Add(LineNum, 2, PostBLL.ReadPost(Info.Department).PostName);
//cells.Add(LineNum, 8, Info.UserName);
//cells.Add(LineNum, 9, BLLPost.ReadPost(Info.PostId).PostName);
//cells.Add(LineNum, 9, PostBLL.ReadPost(Info.StudyPostID).PostName);
//cells.Add(LineNum, 10, AdminGroupBLL.ReadAdminGroupCache(Info.GroupID).Name);
LineNum = LineNum + 1;
}
}
//
//生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
//ScriptHelper.Alert(Server.MapPath("~/Demo.xls"));
if (File.Exists(Server.MapPath(FilePath)))
{
File.Delete(Server.MapPath(FilePath));//删除
}
//保存文档
xls.Save(Server.MapPath(FilePath)); //保存到服务器
xls.Send(); //发送到客户端
}
public void ProcessRequest(HttpContext context)
{
List <DataLists> dataLists = new List <DataLists>();
{
int LocationID = 1;
//List<DataList> dataLists = new List<DataList>();
int RunMachNum = 0;
int AllMachNum;
using (JDJS_WMS_DB_USEREntities wms = new JDJS_WMS_DB_USEREntities())
{
var devices = wms.JDJS_WMS_Device_Info;
AllMachNum = devices.Count();
foreach (var device in devices)
{
int cncids = device.ID;
var state = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (state != null)
{
if (state.ProgState == 1)
{
int cncid = device.ID;
var platecnc = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == cncid);
if (platecnc.Count() > 0)
{
//RunMachNum++;
}
}
}
DataLists data = new DataLists();
data.cncNum = device.MachNum;
data.cncType = device.MachState;
data.State = "/";
data.Time = DateTime.Now.Date.ToString();
var state1 = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (state1 != null)
{
if (state1.ProgState != -1)
{
data.State = "开机";
RunMachNum++;
}
}
var processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 2);
if (processing.Count() > 0)
{
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
var next = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime);
if (next.Count() > 0)
{
int ids = Convert.ToInt32(next.FirstOrDefault().ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == next.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingFileName = OrderNums + "-P" + processNums.ToString();
}
var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != processing.FirstOrDefault().ProcessID).OrderBy(r => r.StartTime);
if (nextTask.Count() > 0)
{
int ids = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingProcess = OrderNums + "-" + processNums.ToString() + "序";
}
}
else
{
processing = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1).OrderBy(r => r.StartTime);
if (processing.Count() > 0)
{
if (processing.Count() > 1)
{
var pro = processing.ToList();
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
int ids = Convert.ToInt32(pro[1].ProcessID);
int processNums = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == ids).FirstOrDefault().ProcessID);
int idsss = Convert.ToInt32(pro[1].OrderID);
string OrderNums = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == idsss).FirstOrDefault().Order_Number.ToString();
data.waitingFileName = OrderNums + "-P" + processNums.ToString();
var nextTask = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 1 && r.ProcessID != ids).OrderBy(r => r.StartTime);
if (nextTask.Count() > 0)
{
int idss = Convert.ToInt32(nextTask.FirstOrDefault().ProcessID);
int processNumss = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == idss).FirstOrDefault().ProcessID);
string OrderNumss = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == nextTask.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.waitingProcess = OrderNumss + "-" + processNumss.ToString() + "序";
}
}
else
{
int id = Convert.ToInt32(processing.FirstOrDefault().ProcessID);
int processNum = Convert.ToInt32(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == id).FirstOrDefault().ProcessID);
string OrderNum = wms.JDJS_WMS_Order_Entry_Table.Where(r => r.Order_ID == processing.FirstOrDefault().OrderID).FirstOrDefault().Order_Number.ToString();
data.doingFileName = OrderNum + "-P" + processNum.ToString();
data.JiaWei = processNum.ToString() + "夹";
data.doingProcess = OrderNum + "-" + processNum.ToString() + "序";
var willdo = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.ProcessID == processing.FirstOrDefault().ProcessID&& r.isFlag == 1);
data.surplusNumber = willdo.Count().ToString();
{
var cncqu = wms.JDJS_WMS_Quickchangbaseplate_Table.Where(r => r.CncID == device.ID);
if (cncqu.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var cncstate = wms.JDJS_WMS_Device_RealTime_Data.Where(r => r.CncID == device.ID).FirstOrDefault();
if (cncstate != null)
{
int states = Convert.ToInt32(cncstate.ProgState);
if (states == 1)
{
var timestate = wms.JDJS_WMS_Device_Times_Data.Where(r => r.ID == device.ID).FirstOrDefault();
if (timestate != null)
{
double timeMin = Convert.ToDouble(timestate.NowRunTime);
double alltime = Convert.ToDouble(wms.JDJS_WMS_Order_Process_Info_Table.Where(r => r.ID == processing.FirstOrDefault().ProcessID).FirstOrDefault().ProcessTime);
double rate = timeMin / alltime;
if (rate < 0)
{
data.progress = "0.000000";
}
else if (rate >= 1)
{
data.progress = "0.999999";
}
else
{
data.progress = rate.ToString("0.000000");
}
}
else
{
data.progress = "0.000000";
}
}
else
{
DateTime time = Convert.ToDateTime(cncqu.FirstOrDefault().time);
double nowtime = (DateTime.Now - time).TotalMinutes;
var info = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == device.ID && r.isFlag == 3).ToList();
if (info.Count() < 1)
{
data.progress = "0.000000";
}
else
{
var timeInfo = info.OrderByDescending(r => r.EndTime);
DateTime oldTime = Convert.ToDateTime(timeInfo.FirstOrDefault().EndTime);
if (oldTime > time)
{
data.progress = "1.000000";
}
else
{
data.progress = "0.000000";
}
}
}
}
else
{
data.progress = "0.000000";
}
}
}
}
}
else
{
data.doingFileName = "无排配";
data.JiaWei = "/";
}
}
var endProcess = wms.JDJS_WMS_Order_Process_Scheduling_Table.Where(r => r.CncID == cncids && r.isFlag != 0);
var end = endProcess.OrderByDescending(r => r.EndTime).FirstOrDefault();
if (end != null)
{
data.StopTime = end.EndTime.ToString();
}
dataLists.Add(data);
}
foreach (var item in dataLists)
{
item.RunMachNum = RunMachNum.ToString();
item.AllMachNum = AllMachNum.ToString();
item.OtherMachNum = (AllMachNum - RunMachNum).ToString();
}
var lists = dataLists.OrderByDescending(r => r.progress);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
var model = new { code = 0, data = lists };
var json = serializer.Serialize(model);
}
}
XlsDocument doc = new XlsDocument();
doc.FileName = DateTime.Now.ToString("yyyyMMddhhmmssms") + "样机开机状况.xls";
Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
Cells cells = sheet.Cells;
ColumnInfo col = new ColumnInfo(doc, sheet); //创建列样式对象
col.ColumnIndexStart = 5; //起始列，索引从0开始
col.ColumnIndexEnd = 5; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col.Width = 8888; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col); //将列样式作用于此工作表
ColumnInfo col2 = new ColumnInfo(doc, sheet); //创建列样式对象
col2.ColumnIndexStart = 2; //起始列，索引从0开始
col2.ColumnIndexEnd = 2; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col2.Width = 4444; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col2); //将列样式作用于此工作表
ColumnInfo col4 = new ColumnInfo(doc, sheet); //创建列样式对象
col4.ColumnIndexStart = 4; //起始列，索引从0开始
col4.ColumnIndexEnd = 4; //结束列，索引从0开始，这样为第1列、第2列使用此样式
col4.Width = 4444; //宽度，字节长度，ushort类型 0~65535
sheet.AddColumnInfo(col4); //将列样式作用于此工作表
MergeArea ma1 = new MergeArea(1, 1, 1, 7); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma1); //添加合并单元格到工作表
MergeArea ma2 = new MergeArea(2, 2, 2, 6); //合并单元格，第2行第5列 到 第3行第7列
sheet.AddMergeArea(ma2); //添加合并单元格到工作表
XF xf = doc.NewXF(); //单元格样式对象
xf.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf.PatternColor = Colors.Green; //填充背景底色
xf.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf.Font.FontName = "黑体"; //字体
xf.Font.Height = 15 * 15; //字体大小
//xf.UseBorder = false ; //使用边框
//xf.BottomLineStyle = 1; //边框样式
//xf.BottomLineColor = Colors.Black; //边框颜色
XF xf1 = doc.NewXF(); //单元格样式对象
xf1.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf1.HorizontalAlignment = HorizontalAlignments.Right; //水平居中
xf1.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf1.PatternColor = Colors.Green; //填充背景底色
xf1.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf1.Font.FontName = "宋体"; //字体
xf1.Font.Height = 13 * 13; //字体大小
//xf1.UseBorder = false ; //使用边框
//xf1.BottomLineStyle = 1; //边框样式
//xf1.BottomLineColor = Colors.Black; //边框颜色
XF xf2 = doc.NewXF(); //单元格样式对象
xf2.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf2.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf2.Pattern = 0; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf2.PatternColor = Colors.Green; //填充背景底色
xf2.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf2.Font.FontName = "宋体"; //字体
xf2.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
XF xf3 = doc.NewXF(); //单元格样式对象
xf3.VerticalAlignment = VerticalAlignments.Centered; //垂直居中
xf3.HorizontalAlignment = HorizontalAlignments.Centered; //水平居中
xf3.Pattern = 1; //填充风格，0为无色填充，1为没有间隙的纯色填充
xf3.PatternColor = Colors.Green; //填充背景底色
xf3.Font.ColorIndex = 0; //字体前景色颜色，未知值
xf3.Font.FontName = "宋体"; //字体
xf3.Font.Height = 13 * 13; //字体大小
//xf2.UseBorder = false; //使用边框
//xf2.BottomLineStyle = 1; //边框样式
//xf2.BottomLineColor = Colors.Black; //边框颜色
cells.Add(1, 1, "智能中心样机开机状况", xf); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(2, 2, "日期：" + DateTime.Now.Date.ToShortDateString(), xf1); //添加单元格内容，第2行，第5列，内容，索引从1开始
cells.Add(3, 1, "类别", xf2);
cells.Add(3, 2, "机台号", xf2);
cells.Add(3, 3, "文件名", xf2);
cells.Add(3, 4, "夹位", xf2);
cells.Add(3, 5, "开机状态", xf2);
cells.Add(3, 6, "CNC预计结束时间", xf2);
cells.Add(3, 7, "备注", xf2);
int index = 4;
for (int i = 0; i < dataLists.Count(); i++)
{
cells.Add(i + 4, 1, dataLists[i].cncType, xf2);
cells.Add(i + 4, 2, dataLists[i].cncNum, xf2);
if (dataLists[i].doingFileName == "无排配")
{
cells.Add(i + 4, 3, dataLists[i].doingFileName, xf2);
cells.Add(i + 4, 4, dataLists[i].JiaWei, xf2);
cells.Add(i + 4, 5, dataLists[i].State, xf2);
}
else
{
cells.Add(i + 4, 3, dataLists[i].doingFileName, xf3);
cells.Add(i + 4, 4, dataLists[i].JiaWei, xf3);
cells.Add(i + 4, 5, dataLists[i].State, xf3);
}
cells.Add(i + 4, 6, dataLists[i].StopTime, xf2);
cells.Add(i + 4, 7, "", xf2);
index++;
}
cells.Add(index, 1, "机台总数", xf);
//cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2);
cells.Add(index, 3, "实际开机数量", xf);
//cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2);
cells.Add(index, 5, "未开机数量", xf);
//cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2);
if (dataLists.Count() > 0)
{
int lastIndex = dataLists.Count() - 1;
//cells.Add(index, 1, "机台总数", xf);
cells.Add(index, 2, dataLists[lastIndex].AllMachNum, xf2);
//cells.Add(index, 3, "实际开机数量", xf);
cells.Add(index, 4, dataLists[lastIndex].RunMachNum, xf2);
//cells.Add(index, 5, "未开机数量", xf);
cells.Add(index, 6, dataLists[lastIndex].OtherMachNum, xf2);
}
PathInfo pathInfo = new PathInfo();
string path = pathInfo.upLoadPath() + @"ExcelFile\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
doc.Save(path);
context.Response.Write(pathInfo.downLoadPath() + @"ExcelFile\" + doc.FileName);
}
protected void ExcelOut1(int CompanyId)
{
string fileName = "Demo" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
//生成Excel开始
string FilePath = "~/xml/" + fileName;
XlsDocument xls = new XlsDocument(); //创建空xls文档
xls.FileName = fileName; //保存路径，如果直接发送到客户端的话只需要名称 生成名称
//设置指定工作页跨行跨列结束
//创建列样式创建列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
//cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
//cellXF.PatternBackgroundColor = Colors.Red;//填充的背景底色
//cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
//设置指定工作页跨行跨列
MergeArea ma = new MergeArea(1, 1, 1, 8);//合并单元格 行与列
//创建数据
string sonCompanyID = CompanyBLL.ReadCompanyIdList(CompanyId.ToString());
List <CompanyInfo> companyList = CompanyBLL.ReadCompanyListByCompanyId(sonCompanyID);
foreach (CompanyInfo company in companyList)
{
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(company.CompanySimpleName); //创建一个工作页为Dome
sheet.AddMergeArea(ma);
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//列操作基本
Cell cell = cells.Add(1, 1, company.CompanyName, cellXF);
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = true; //设置粗体
cell.Font.ColorIndex = 0; //设置颜色码
cell.Font.FontFamily = FontFamilies.Roman; //设置字体 默认为宋体
//创建列结束
UserSearchInfo userSearch = new UserSearchInfo();
userSearch.InCompanyID = company.CompanyId.ToString();
userSearch.StatusNoEqual = (int)UserState.Del;
List <UserInfo> userList = UserBLL.SearchUserList(userSearch);
int LineNum = 3;
if (userList.Count > 0)
{
cells.Add(2, 1, "序号");
cells.Add(2, 2, "姓名");
cells.Add(2, 3, "手机");
cells.Add(2, 4, "用户ID");
cells.Add(2, 5, "学习岗位");
cells.Add(2, 6, "帐户类型");
cells.Add(2, 7, "最近登陆时间");
cells.Add(2, 8, "状态");
foreach (UserInfo Info in userList)
{
cells.Add(LineNum, 1, LineNum - 2);
cells.Add(LineNum, 2, Info.RealName);
cells.Add(LineNum, 3, Info.Mobile);
cells.Add(LineNum, 4, Info.UserName);
cells.Add(LineNum, 5, PostBLL.ReadPost(Info.StudyPostID).PostName);
cells.Add(LineNum, 6, AdminGroupBLL.ReadAdminGroupCache(Info.GroupID).Name);
cells.Add(LineNum, 7, Info.RegisterDate == Info.LastLoginDate ? "" : Info.LastLoginDate.ToString());
cells.Add(LineNum, 8, EnumHelper.ReadEnumChineseName <UserState>(Info.Status));
LineNum = LineNum + 1;
}
}
}
//
//生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
//ScriptHelper.Alert(Server.MapPath("~/Demo.xls"));
if (File.Exists(Server.MapPath(FilePath)))
{
File.Delete(Server.MapPath(FilePath));//删除
}
//保存文档
xls.Save(Server.MapPath(FilePath)); //保存到服务器
xls.Send(); //发送到客户端
}
public void CreateExcel(string fileName, ArrayList title, DataTable DataSource)
{
XlsDocument xls = new XlsDocument();
#region Sheet标题样式
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = true; // 使用边框
titleXF.BottomLineStyle = 1; // 下边框样式
titleXF.BottomLineColor = Colors.Black; // 下边框颜色
titleXF.Font.FontName = "宋体"; // 字体
titleXF.Font.Bold = true; // 是否加楚
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
#endregion
#region 列标题样式
XF columnTitleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
columnTitleXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
columnTitleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
columnTitleXF.UseBorder = true; // 使用边框
columnTitleXF.TopLineStyle = 1; // 上边框样式
columnTitleXF.TopLineColor = Colors.Black; // 上边框颜色
columnTitleXF.BottomLineStyle = 1; // 下边框样式
columnTitleXF.BottomLineColor = Colors.Black; // 下边框颜色
columnTitleXF.LeftLineStyle = 1; // 左边框样式
columnTitleXF.LeftLineColor = Colors.Black; // 左边框颜色
columnTitleXF.RightLineStyle = 1; // 右边框样式
columnTitleXF.RightLineColor = Colors.Black; // 右边框颜色
#endregion
#region 数据单元格样式
XF dataXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXF.UseBorder = true; // 使用边框
dataXF.TopLineStyle = 1; // 上边框样式
dataXF.TopLineColor = Colors.Black; // 上边框颜色
dataXF.BottomLineStyle = 1; // 下边框样式
dataXF.BottomLineColor = Colors.Black; // 下边框颜色
dataXF.LeftLineStyle = 1; // 左边框样式
dataXF.LeftLineColor = Colors.Black; // 左边框颜色
dataXF.RightLineStyle = 1; // 右边框样式
dataXF.RightLineColor = Colors.Black; // 右边框颜色
dataXF.Font.FontName = "宋体";
dataXF.Font.Height = 9 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXF.TextWrapRight = true; // 自动换行
#endregion
#region 数据单元格样式
XF dataXFchange = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXFchange.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXFchange.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXFchange.UseBorder = true; // 使用边框
dataXFchange.TopLineStyle = 1; // 上边框样式
dataXFchange.TopLineColor = Colors.Black; // 上边框颜色
dataXFchange.BottomLineStyle = 1; // 下边框样式
dataXFchange.BottomLineColor = Colors.Black; // 下边框颜色
dataXFchange.LeftLineStyle = 1; // 左边框样式
dataXFchange.LeftLineColor = Colors.Black; // 左边框颜色
dataXFchange.RightLineStyle = 1; // 右边框样式
dataXFchange.RightLineColor = Colors.Black; // 右边框颜色
dataXFchange.Font.FontName = "宋体";
dataXFchange.Font.Height = 9 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXFchange.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXFchange.TextWrapRight = true; // 自动换行
dataXFchange.Pattern = 1; // 单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色
dataXFchange.PatternBackgroundColor = Colors.Red; // 填充的底色
dataXFchange.PatternColor = Colors.Default2F; // 填充背景色
#endregion
for (int i = 1; i <= sheetCount; i++)
{
// 根据计算出来的Sheet数量，一个个创建
// 行和列的设置需要添加到指定的Sheet中，且每个设置对象不能重用（因为可以设置起始和终止行或列，就没有太大必要重用了，这应是一个策略问题）
Worksheet sheet;
if (sheetCount == 1)
{
sheet = xls.Workbook.Worksheets.Add(sheetName);
}
else
{
sheet = xls.Workbook.Worksheets.Add(sheetName + " - " + i);
}
ColumnInfo columnInfo = new ColumnInfo(xls, sheet);
columnInfo.ColumnIndexStart = 0;
columnInfo.ColumnIndexEnd = (ushort)(DataSource.Columns.Count - 1);
columnInfo.Width = 15 * 330;
sheet.AddColumnInfo(columnInfo);
// 合并单元格
//sheet.Cells.Merge(1, 1, 1, 4);
MergeArea titleArea = new MergeArea(1, 1, 1, Column); // 一个合并单元格实例(合并第1行、第1列 到 第1行、第4列)
sheet.AddMergeArea(titleArea); //填加合并单元格
// 开始填充数据到单元格
Cells cells = sheet.Cells;
// Sheet标题行，行和列的索引都是从1开始的
Cell cell = cells.Add(1, 1, sheetName, titleXF);
for (i = 2; i <= Column; i++)
{
cells.Add(1, i, "", titleXF);
}
for (int j = 0; j < DataSource.Columns.Count; j++)
{
cells.Add(2, j + 1, DataSource.Columns[j].ColumnName, columnTitleXF);
}
if (DataSource.Rows.Count == 0)
{
cells.Add(3, 1, "没有可用数据");
return;
}
for (int l = 0; l < DataSource.Rows.Count; l++)
{
string datecontrol = DataSource.Rows[l][DataSource.Columns.Count - 1].ToString();
string morgate = DataSource.Rows[l][DataSource.Columns.Count - 3].ToString();
string datenow = DateTime.Now.ToString("yyyy/M/d");
DateTime dt1 = Convert.ToDateTime(datecontrol);
DateTime dt2 = Convert.ToDateTime(datenow);
TimeSpan ts = dt1 - dt2;
int sub = ts.Days;
for (int j = 0; j < DataSource.Columns.Count; j++)
{
if (sub <= 0 && morgate == "未抵押")
{
cells.Add(l + 3, j + 1, DataSource.Rows[l][j].ToString(), dataXFchange);
}
else
{
cells.Add(l + 3, j + 1, DataSource.Rows[l][j].ToString(), dataXF);
}
}
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "保存车辆抵押信息到Excel文件";
saveFileDialog.Filter = "Excel文件|*.xls";
saveFileDialog.FileName = fileName;
saveFileDialog.RestoreDirectory = true;
DialogResult result = saveFileDialog.ShowDialog();
if (result == DialogResult.OK)
{
int fileI = saveFileDialog.FileName.LastIndexOf("\\");
xls.FileName = saveFileDialog.FileName.Substring(fileI + 1, saveFileDialog.FileName.Length - (fileI + 1));
string path = saveFileDialog.FileName.Substring(0, fileI + 1);
try
{
xls.Save(path, true);
}
catch (Exception ex)
{
MessageBox.Show("未导出成功，文件可能被占用", "提示", MessageBoxButtons.OK);
}
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK);
}
}
}
