protected virtual void SetSheetColumnsWith(Worksheet worksheet, int sheetIndex, List <ColumnData> columnSetting, DataTable data)
{
for (int i = 0; i < columnSetting.Count; i++)
{
ColumnInfo col;
if (!columnSetting[i].Width.HasValue)
{
// 如果是DateTime类型的列，设默认长度20；
if (data != null && data.Rows != null && data.Rows.Count > 0)
{
int rowIndex = 0;
object t = null;
while ((t == null || t == DBNull.Value) && rowIndex < data.Rows.Count)
{
t = data.Rows[rowIndex][columnSetting[i].FieldIndex.Value];
rowIndex++;
}
if (t != null && t != DBNull.Value && (t.GetType() == typeof(DateTime) || t.GetType() == typeof(DateTime?)))
{
col = new ColumnInfo(this.m_xlsDocument, worksheet);
col.Width = (ushort)(20 * this.COLUMN_WIDTH_SCALE);
col.ColumnIndexStart = (ushort)(i);
col.ColumnIndexEnd = (ushort)(i);
worksheet.AddColumnInfo(col);
}
}
continue;
}
col = new ColumnInfo(this.m_xlsDocument, worksheet);
col.Width = (ushort)(columnSetting[i].Width.Value * this.COLUMN_WIDTH_SCALE);
col.ColumnIndexStart = (ushort)(i);
col.ColumnIndexEnd = (ushort)(i);
worksheet.AddColumnInfo(col);
}
}
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>
/// 绑定数据库生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = Server.UrlEncode(xlsName);
int rowIndex = 1;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 20 * 256;
sheet.AddColumnInfo(colInfo);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
Cells cells = sheet.Cells;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col.ColumnName, xf);
cell.Font.Bold = true;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
}
xls.Send();
Response.Flush();
Response.End();
}
/// <summary>
/// 调整表格各列的宽度
/// </summary>
private void AdjustColumnWidth()
{
for (ushort i = 1; i <= columnCount; i++)
{
int width = GetMaxColumnWidth(i);
ColumnInfo columnInfo = new ColumnInfo(xls, worksheet);
columnInfo.ColumnIndexStart = (ushort)(i - 1); //索引从0开始
columnInfo.ColumnIndexEnd = (ushort)(i - 1); //索引从0开始
columnInfo.Width = (ushort)((width + 2) * 256);
worksheet.AddColumnInfo(columnInfo);
}
;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="path"></param>
public void Export(string path)
{
PrintHeader();
PrintContent();
PrintFooter();
ColumnInfo colInfo = new ColumnInfo(XlsDoc, CurSheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = (ushort)(Content.ColumnsCount - 1);
colInfo.Width = 256 * 15;
CurSheet.AddColumnInfo(colInfo);
XlsDoc.FileName = Path.GetFileName(path);
XlsDoc.Save(Path.GetDirectoryName(path), true);
}
protected void CreateExcelColumns(Worksheet worksheet, ushort width, params ushort[] colIndexs)
{
if (colIndexs.Length > 0)
{
ColumnInfo col;
for (int i = 0; i < colIndexs.Length; i++)
{
col = new ColumnInfo(this.m_xlsDocument, worksheet);
col.Width = (ushort)(width * this.COLUMN_WIDTH_SCALE);
col.ColumnIndexStart = colIndexs[i];
col.ColumnIndexEnd = colIndexs[i];
worksheet.AddColumnInfo(col);
}
}
}
/// <summary>
/// Cria a tabela que receberá os dados na planilha
/// </summary>
public static void CreateDataTable(String[] columnNames, int[] columnWidths, int rowCount)
{
// Define o nome da planilha e a largura das colunas
fileStream = new FileStream(@"C:\work\CadastroProdutos.xls", FileMode.Create, FileAccess.ReadWrite);
document = new XlsDocument();
worksheet = document.Workbook.Worksheets.Add("Cadastro de Produtos");
ColumnInfo info = new ColumnInfo(document, worksheet);
info.ColumnIndexStart = 1;
info.ColumnIndexEnd = (ushort)columnNames.Length;
info.Width = 5100;
worksheet.AddColumnInfo(info);
// Prepara o plano de fundo da planilha
for (int row = 1; row < rowCount + 10; row++)
{
for (int col = 1; col < columnNames.Length + 3; col++)
{
SetCellPattern(row, col);
}
}
// Insere o título (agrupa as celulas que fazem parte do título)
worksheet.Cells.Merge(5, 5, 2, columnNames.Length + 1);
Cell titleCell = worksheet.Cells.Add(5, 2, "Cadastro de Produtos");
titleCell.HorizontalAlignment = HorizontalAlignments.Centered;
titleCell.Font.Height = 400;
titleCell.Font.Bold = true;
titleCell.Font.FontFamily = FontFamilies.Roman;
// Cria celulas com os nomes das colunas
for (int ndx = 0; ndx < columnNames.Length; ndx++)
{
Cell columnName = worksheet.Cells.Add(6, ndx + 2, columnNames[ndx]);
columnName.Font.Bold = true;
columnName.PatternColor = Colors.Default1F;
columnName.Pattern = 1;
SetCellBorder(columnName);
}
}
/// <summary>
/// Cria a tabela que receberá os dados do relatório
/// </summary>
public void CreateDataTable(String[] columnNames, int[] columnWidths, int rowCount)
{
// Define o nome da planilha e a largura das colunas
reportSheet = document.Workbook.Worksheets.Add(reportHeaders[0]);
ColumnInfo info = new ColumnInfo(document, reportSheet);
info.ColumnIndexStart = 1;
info.ColumnIndexEnd = (ushort)columnNames.Length;
info.Width = 5100;
reportSheet.AddColumnInfo(info);
// Prepara o plano de fundo da planilha
this.rowCount = rowCount;
for (int row = 1; row < rowCount + 10; row++)
{
for (int col = 1; col < columnNames.Length + 3; col++)
{
SetCellPattern(row, col);
}
}
// Insere o cabeçalho da planilha
ProcessHeaders(columnNames.Length);
// Cria celulas com os nomes das colunas
for (int ndx = 0; ndx < columnNames.Length; ndx++)
{
Cell columnName = reportSheet.Cells.Add(6, ndx + 2, columnNames[ndx]);
columnName.Font.Bold = true;
columnName.PatternColor = Colors.Default1F;
columnName.Pattern = 1;
SetCellBorder(columnName);
}
// Cria o totalizador
totalizer = new ReportTotalizer(columnNames.Length);
}
/// <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);
}
/// <summary>
/// DataSet转Excel（SheetName使用对应DataTable的TableName）。
/// 不支持在已存在的Excel文件中添加新的sheet（如果文件已存在，则会覆盖）。
/// </summary>
/// <param name="ds">待写入Excel的DataSet对象</param>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="isColumnWritten">DataTable的列名是否要写入</param>
/// <param name="cellStyle">单元格格式。如果为null，则不设置单元格格式</param>
public static void ToExcel(DataSet ds, string excelFilePath, bool isColumnWritten, CellStyle cellStyle)
{
if (string.IsNullOrEmpty(excelFilePath))
{
throw new ArgumentException(ErrorFilePath);
}
if (ds == null || ds.Tables.Count <= 0)
{
return;
}
//创建Excel文档
XlsDocument xls = new XlsDocument();
xls.SummaryInformation.Author = Author;
xls.SummaryInformation.Subject = Subject;
xls.SummaryInformation.Comments = Comments;
xls.DocumentSummaryInformation.Company = Company;
foreach (DataTable dt in ds.Tables)
{
if (dt == null || dt.Rows.Count <= 0)
{
continue;
}
int cellRow = 1;//从第几行开始写数据
string sheetName = GetValidSheetName(dt.TableName);
if (string.IsNullOrEmpty(sheetName) || xls.Workbook.Worksheets.Any(worksheet => worksheet.Name == sheetName))
{
sheetName = string.Format("Sheet{0}", xls.Workbook.Worksheets.Count + 1);
}
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
sheet.SheetType = WorksheetTypes.Worksheet;
Cells cells = sheet.Cells;
//写标题
if (isColumnWritten)
{
#region 设置标题格式
XF xfDataHead = xls.NewXF();
if (cellStyle != null)
{
xfDataHead.HorizontalAlignment = cellStyle.TitleFontCenter ? HorizontalAlignments.Centered : HorizontalAlignments.Left;
xfDataHead.Font.FontName = cellStyle.TitleFontName;
xfDataHead.Font.Bold = cellStyle.TitleFontBold;
xfDataHead.Font.Height = cellStyle.TitleFontSize;
if (cellStyle.Border)
{
xfDataHead.UseBorder = true;
xfDataHead.TopLineStyle = 1;
xfDataHead.BottomLineStyle = 1;
xfDataHead.LeftLineStyle = 1;
xfDataHead.RightLineStyle = 1;
}
xfDataHead.CellLocked = false;
xfDataHead.UseProtection = false;
xfDataHead.UseNumber = true;
xfDataHead.UseMisc = true;
}
#endregion
for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++)
{
if (cellStyle != null)
{
cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption, xfDataHead);
}
else
{
cells.Add(cellRow, cellColumn, dt.Columns[cellColumn - 1].Caption);
}
}
cellRow++;
}
#region 设置内容格式
XF xfDataContent = xls.NewXF();
if (cellStyle != null)
{
xfDataContent.HorizontalAlignment = HorizontalAlignments.Left;
xfDataContent.Font.FontName = cellStyle.ContentFontName;
xfDataContent.Font.Height = cellStyle.ContentFontSize;
if (cellStyle.Border)
{
xfDataContent.UseBorder = true;
xfDataContent.TopLineStyle = 1;
xfDataContent.BottomLineStyle = 1;
xfDataContent.LeftLineStyle = 1;
xfDataContent.RightLineStyle = 1;
}
xfDataContent.CellLocked = false;
xfDataContent.UseProtection = false;
xfDataContent.UseNumber = true;
xfDataContent.UseMisc = true;
}
#endregion
//写内容
foreach (DataRow dr in dt.Rows)
{
for (int cellColumn = 1; cellColumn <= dt.Columns.Count; cellColumn++)
{
if (cellStyle != null)
{
ColumnInfo colInfo = new ColumnInfo(xls, sheet)
{
ColumnIndexStart = (ushort)(cellColumn - 1),
ColumnIndexEnd = (ushort)(cellColumn - 1),
Width = cellStyle.ColumnWidth
};
sheet.AddColumnInfo(colInfo);
}
object obj = dr[cellColumn - 1] != DBNull.Value ? (dt.Columns[cellColumn - 1].DataType == typeof(DateTime)
? Convert.ToDateTime(dr[cellColumn - 1]).ToString("yyyy-MM-dd HH:mm:ss")
: dr[cellColumn - 1])
: string.Empty;
if (cellStyle != null)
{
cells.Add(cellRow, cellColumn, obj, xfDataContent);
}
else
{
cells.Add(cellRow, cellColumn, obj);
}
}
cellRow++;
}
}
xls.FileName = Path.GetFileName(excelFilePath);
xls.Save(Path.GetDirectoryName(excelFilePath), true);
//xls.Send(XlsDocument.SendMethods.Attachment);//浏览器保存文件
}
/// <summary>
/// 绑定数据库生成XLS报表
/// </summary>
/// <param name="ds">获取DataSet数据集</param>
/// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = Server.UrlEncode(xlsName);
int rowIndex = 2;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 17 * 256;
sheet.AddColumnInfo(colInfo);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 6);
MergeRegion(ref sheet, xf, "分公司", 2, 2, 1, 1);
MergeRegion(ref sheet, xf, "外包单位", 2, 2, 2, 2);
MergeRegion(ref sheet, xf, "障碍与服务指标（85分）", 2, 2, 3, 3);
MergeRegion(ref sheet, xf, "基础管理考核（15分）", 2, 2, 4, 4);
MergeRegion(ref sheet, xf, "额外奖罚", 2, 2, 5, 5);
MergeRegion(ref sheet, xf, "汇总得分", 2, 2, 6, 6);
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
if (colIndex != 1)
{
cell.Font.Bold = false; //字体为粗体
}
}
}
MergeRegion(ref sheet, xf, "设计院", 3, 4, 2, 2);
MergeRegion(ref sheet, xf, "北京合力", 5, 8, 2, 2);
xls.Send();
Response.Flush();
Response.End();
}
///// <summary>
///// 绑定数据库生成XLS报表
///// </summary>
///// <param name="ds">获取DataSet数据集</param>
///// <param name="xlsName">报表表名</param>
private void xlsGridview(DataTable dt, string xlsName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = System.Web.HttpUtility.UrlEncode(xlsName, System.Text.Encoding.UTF8);
int rowIndex = 5;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置列格式
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 8;
colInfo.Width = 18 * 256;
sheet.AddColumnInfo(colInfo);
//设置样式
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
MergeRegion(ref sheet, xf, xlsName.Substring(0, xlsName.Length - 4), 1, 1, 1, 10);
MergeRegion(ref sheet, xf, "姓名", 2, 5, 1, 1);
MergeRegion(ref sheet, xf, "日常工作（权重30分）", 2, 2, 2, 4);
MergeRegion(ref sheet, xf, "KPI考核（权重60分）", 2, 2, 5, 8);
MergeRegion(ref sheet, xf, "胜任度(权重10分)", 2, 2, 9, 9);
MergeRegion(ref sheet, xf, "得分", 2, 5, 10, 10);
Cell cell1 = cells.Add(3, 2, "A", xf);
Cell cell2 = cells.Add(3, 3, "B", xf);
Cell cell3 = cells.Add(3, 4, "C", xf);
Cell cell4 = cells.Add(3, 5, "D", xf);
Cell cell5 = cells.Add(3, 6, "E", xf);
MergeRegion(ref sheet, xf, "F", 3, 3, 7, 8);
Cell cell6 = cells.Add(3, 9, "G", xf);
Cell cell7 = cells.Add(4, 2, "劳动纪律", xf);
Cell cell8 = cells.Add(4, 3, "信息报道", xf);
Cell cell9 = cells.Add(4, 4, "基础报表", xf);
Cell cell10 = cells.Add(4, 5, "网运指标", xf);
Cell cell11 = cells.Add(4, 6, "工作效率", xf);
MergeRegion(ref sheet, xf, "其它加扣分", 4, 4, 7, 8);
Cell cell12 = cells.Add(4, 9, "贡献度胜任度职业道德", xf);
Cell cell13 = cells.Add(5, 2, "迟到或早退一次扣1分、事假每天扣2分，病假每天扣1分执行。无故旷工半天，扣5分。无故旷工一天，扣10分，扣分随天数增加而增加.", xf);
Cell cell14 = cells.Add(5, 3, "每人每月至少上报信息1篇，每差一篇扣5分。凡信息被市公司采用的加2分，被省公司采用的加5分。", xf);
Cell cell15 = cells.Add(5, 4, "未按时出规定报表一次扣2分，被省公司通报一次扣5分。", xf);
Cell cell16 = cells.Add(5, 5, "本岗位挂靠指标完成情况", xf);
Cell cell17 = cells.Add(5, 6, "不能按时按要求完成本岗位工作任务（周工作计划内容、日常工作内容），效率低的每次每项扣2分", xf);
MergeRegion(ref sheet, xf, "全省有通报的各项工作中，排名前三加5分，排名前6加3分，低于全省平均水平扣2分，排名12名之后扣3分，排名后三扣5分", 5, 5, 7, 8);
Cell cell18 = cells.Add(5, 9, "部门员工得分=部门正职得分*50+部门副职得分*30%+员工互评得分*20%; 部门副职得分=员工打分均值", xf);
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
}
xls.Send();
Response.Flush();
Response.End();
}
/// <summary>
/// 导出SO的excel通用方法
/// </summary>
/// <param name="myData">导出的数据</param>
private void ExportExcel(List <ExcelData> myData)
{
//列宽：
ushort[] colWidth = new ushort[] { 16, 16, 16, 14, 18, 16, 28, 28, 16, 20,
16, 14, 18, 24, 32, 14, 14, 14, 14, 14,
14, 16, 14, 14, 14, 14, 14, 18, 18, 60 };
//列名：
string[] colName = new string[] { "审核结果", "流水号", "订单号", "下单日期", "办事处", "订单类型", "购货客户", "国外客户", "产品类别", "产品用途",
"币别", "汇率", "产品代码", "产品名称", "规格型号", "单位", "数量", "单价", "含税单价", "成交价",
"成本", "价税合计", "费用率%", "MU%", "税率%", "佣金率%", "佣金", "发货地点", "交货属性", "摘要" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = string.Format("销售订单_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
Worksheet sheet = xls.Workbook.Worksheets.Add("订单信息列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in myData)
{
colIndex = 1;
cells.Add(++rowIndex, colIndex, d.auditStatus);
cells.Add(rowIndex, ++colIndex, d.h.sys_no);
cells.Add(rowIndex, ++colIndex, d.h.order_no);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.h.order_date).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.h.agency1_name);
cells.Add(rowIndex, ++colIndex, d.h.order_type_name);
cells.Add(rowIndex, ++colIndex, d.h.customer_name);
cells.Add(rowIndex, ++colIndex, d.h.oversea_customer_name);
cells.Add(rowIndex, ++colIndex, d.h.product_type_name);
cells.Add(rowIndex, ++colIndex, d.h.product_use);
cells.Add(rowIndex, ++colIndex, d.h.currency_name);
cells.Add(rowIndex, ++colIndex, d.h.exchange_rate);
cells.Add(rowIndex, ++colIndex, d.e.item_no);
cells.Add(rowIndex, ++colIndex, d.e.item_name);
cells.Add(rowIndex, ++colIndex, d.e.item_model);
cells.Add(rowIndex, ++colIndex, d.e.unit_name);
cells.Add(rowIndex, ++colIndex, d.e.qty);
cells.Add(rowIndex, ++colIndex, d.e.unit_price);
cells.Add(rowIndex, ++colIndex, d.e.tax_price);
cells.Add(rowIndex, ++colIndex, d.e.deal_price);
cells.Add(rowIndex, ++colIndex, d.e.cost);
cells.Add(rowIndex, ++colIndex, d.e.qty * d.e.tax_price);
cells.Add(rowIndex, ++colIndex, d.e.fee_rate);
cells.Add(rowIndex, ++colIndex, d.e.MU);
cells.Add(rowIndex, ++colIndex, d.e.tax_rate);
cells.Add(rowIndex, ++colIndex, d.e.commission_rate);
cells.Add(rowIndex, ++colIndex, d.e.commission);
cells.Add(rowIndex, ++colIndex, d.h.delivery_place_name);
cells.Add(rowIndex, ++colIndex, d.h.receive_place_name);
cells.Add(rowIndex, ++colIndex, d.h.summary);
}
//合计行
cells.Add(++rowIndex, 1, "合计:");
cells.Add(rowIndex, 17, myData.Sum(d => d.e.qty));
cells.Add(rowIndex, 22, myData.Sum(d => d.e.qty * d.e.tax_price));
xls.Send();
}
public void ExportPOData(string queryJson)
{
GetK3POParams p = JsonConvert.DeserializeObject <GetK3POParams>(queryJson);
p.poNumbers = p.poNumbers == null ? "" : p.poNumbers.Trim();//去掉前后空格
p.itemInfo = p.itemInfo == null ? "" : p.itemInfo.Trim();
p.account = currentAccount;
p.userId = currentUser.userId;
p.userNumber = currentUser.userName;
p.k3HasAudit = true;
List <K3POs> result;
List <decimal> qtyList;
try {
result = new DRSv().GetPOs(p).OrderBy(po => po.poDate).Take(1000).ToList();
//一次性关联1000条到数据库查询会导致内存溢出，所以大于500条的，分2次获取
var infos = result.Take(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList = new DRSv().GetPOTransitQty(infos);
if (result.Count() > 500)
{
infos = result.Skip(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList.AddRange(new DRSv().GetPOTransitQty(infos));
}
}
catch {
return;
}
ushort[] colWidth = new ushort[] { 18, 10, 20, 24, 32, 16,
16, 16, 16, 12, 24, 12, 16,
12, 12, 16, 12, 12, 16 };
string[] colName = new string[] { "订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
"入库数量", "申请数量", "可申请数量", "订料员", "申购部门", "单位", "物料编码",
"订单类型", "采购方式", "订单日期", "贸易类型", "币别", "采购员" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = "订单明细表_" + DateTime.Now.ToString("MMddHHmmss");
Worksheet sheet = xls.Workbook.Worksheets.Add("订单列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
for (var i = 0; i < result.Count(); i++)
{
var d = result[i];
var q = qtyList[i];
colIndex = 1;
//"订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
//"入库数量", "申请数量", "可申请数量", "订料员", "申购部门","单位","物料编码",
//"订单类型","采购方式","订单日期","贸易类型","币别","采购员"
cells.Add(++rowIndex, colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.realteQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", q));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty - d.realteQty - q));
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.departmentName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
}
xls.Send();
WLog("导出K3订单Excel", "行数：" + result.Count() + ";" + queryJson);
//return "已成功导出行数："+result.Count();
}
/// <summary>
/// 导出CH的excel通用方法
/// </summary>
/// <param name="myData">导出的数据</param>
private void ExportExcel(List <ExcelData> myData)
{
//列名：
string[] colName = new string[] { "审核结果", "公司", "流水号", "订单类型", "下单日期", "制单人", "产品类别", "客户编码", "客户名称", "营业员", "付款条款",
"摘要", "产品代码", "产品名称", "规格型号", "单位", "数量", "含税单价", "金额", "税率%", "交货日期",
"备料单号", "BOM表码", "备注" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = string.Format("仪器工业申请单_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
Worksheet sheet = xls.Workbook.Worksheets.Add("订单信息列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colName.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(18 * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in myData)
{
colIndex = 1;
//"审核结果","公司","流水号","下单日期","制单人","产品类别","客户编码","客户名称","营业员","付款条款",
//"摘要","产品代码","产品名称","规格型号","单位","数量","含税单价","金额","税率%","交货日期",
//"备料单号","BOM表码","备注"
cells.Add(++rowIndex, colIndex, d.auditStatus);
cells.Add(rowIndex, ++colIndex, d.h.account);
cells.Add(rowIndex, ++colIndex, d.h.sys_no);
cells.Add(rowIndex, ++colIndex, d.h.order_type_name);
cells.Add(rowIndex, ++colIndex, d.h.apply_time.ToShortDateString());
cells.Add(rowIndex, ++colIndex, d.h.applier_name);
cells.Add(rowIndex, ++colIndex, d.h.product_type_name);
cells.Add(rowIndex, ++colIndex, d.h.customer_no);
cells.Add(rowIndex, ++colIndex, d.h.customer_name);
cells.Add(rowIndex, ++colIndex, d.h.clerk_name);
cells.Add(rowIndex, ++colIndex, d.h.clear_type_name);
cells.Add(rowIndex, ++colIndex, d.h.summary);
cells.Add(rowIndex, ++colIndex, d.e.product_no);
cells.Add(rowIndex, ++colIndex, d.e.product_name);
cells.Add(rowIndex, ++colIndex, d.e.product_model);
cells.Add(rowIndex, ++colIndex, d.e.unit_name);
cells.Add(rowIndex, ++colIndex, d.e.qty);
cells.Add(rowIndex, ++colIndex, d.e.tax_price);
cells.Add(rowIndex, ++colIndex, d.e.qty * d.e.tax_price);
cells.Add(rowIndex, ++colIndex, d.e.tax_rate);
cells.Add(rowIndex, ++colIndex, d.e.fetch_date.ToShortDateString());
cells.Add(rowIndex, ++colIndex, d.e.bl_no);
cells.Add(rowIndex, ++colIndex, d.e.bom_no);
cells.Add(rowIndex, ++colIndex, d.e.comment);
}
xls.Send();
}
/// < summary>
/// 保存Excel
/// neo 2010-12-02
/// < /summary>
/// < param name="ExcelData"> < /param>
/// < param name="sExcelFileName"> < /param>
/// < param name="sExcelDir"> < /param>
/// < param name="sSheetName"> < /param>
public static string SaveAsExcel(DataTable ExcelData, string sExcelDir, string sSheetName)
{
if (ExcelData == null)
{
return(string.Empty);
}
// 生成随机唯一文件名
string sExcelFileName = Guid.NewGuid().ToString() + ".xls";
// 创建Excel文档
XlsDocument ExcelDoc = new XlsDocument();
ExcelDoc.FileName = sExcelFileName;
// 添加Sheet
Worksheet Sheet = ExcelDoc.Workbook.Worksheets.Add(sSheetName);
//Sheet.Name = sSheetName;
// 添加第一行列名
ColumnInfo ColumnInfo1 = new ColumnInfo(ExcelDoc, Sheet);
ColumnInfo1.ColumnIndexStart = 0;
ColumnInfo1.ColumnIndexEnd = Convert.ToUInt16(ExcelData.Columns.Count - 1);
ColumnInfo1.Width = 25 * 200;
Sheet.AddColumnInfo(ColumnInfo1);
int i = 1;
foreach (DataColumn ColumnObj in ExcelData.Columns)
{
Cell CellObj = Sheet.Cells.Add(1, i, ColumnObj.ColumnName);
CellObj.Font.Weight = FontWeight.Bold;
CellObj.Pattern = 1;
CellObj.PatternColor = Colors.Silver;
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
i++;
}
int j = 2;
foreach (DataRow RowObj in ExcelData.Rows)
{
int m = 1;
foreach (DataColumn ColumnObj in ExcelData.Columns)
{
object oValue = RowObj[ColumnObj.ColumnName];
if (oValue.GetType().FullName == "System.Byte")
{
oValue = Convert.ToInt16(oValue);
}
else if (oValue.GetType().FullName == "System.DBNull")
{
oValue = string.Empty;
}
else if (oValue.GetType().FullName == "System.String")
{
oValue = oValue.ToString();
}
else if (oValue.GetType().FullName == "System.DateTime")
{
oValue = Convert.ToDateTime(oValue).ToString();
}
Cell CellObj = Sheet.Cells.Add(j, m, oValue);
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
m++;
}
j++;
}
string sPath = sExcelDir + sExcelFileName;
if (File.Exists(sPath) == true)
{
File.Delete(sPath);
}
ExcelDoc.Save(sExcelDir, true);
return(sPath);
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
if (gvPaymentList.Rows.Count > 0)
{
beginDate = dtpBeginDate.Value.Date;
endDate = dtpEndDate.Value.AddDays(1).Date;
IList <MRS.Model.AccountBook> accountBooks = new List <MRS.Model.AccountBook>();
accountBooks = bllAccountBook.Select_AccountBook_PaymentList(beginDate, endDate);
System.Text.StringBuilder fileName = new StringBuilder();
fileName.Append("医保报销帐单 ");
fileName.Append(DateTime.Now.Year.ToString());
fileName.Append(DateTime.Now.Month.ToString().PadLeft(2, '0'));
fileName.Append(DateTime.Now.Day.ToString().PadLeft(2, '0'));
fileName.Append(".xls");
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = fileName.ToString();
//定义Excel文档属性.
xlsDoc.SummaryInformation.Author = "Cobe lee";
xlsDoc.SummaryInformation.Title = "医保报销帐单";
xlsDoc.SummaryInformation.Comments = "This workbook generated by ZPITC! http://www.tiyi.biz";
xlsDoc.SummaryInformation.Subject = "医保报销帐单";
xlsDoc.DocumentSummaryInformation.Company = "镇海石化工业贸易有限责任公司";
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add("Sheet1");
xlsDoc.Workbook.Worksheets.Add("Sheet2");
xlsDoc.Workbook.Worksheets.Add("Sheet3");
Cells cells = sheet.Cells;
//以下定义 银行帐号 及 身份证号 的列宽.
ColumnInfo col0 = new ColumnInfo(xlsDoc, sheet);
col0.ColumnIndexEnd = 0;
col0.ColumnIndexStart = 0;
col0.Width = 21 * 269;
sheet.AddColumnInfo(col0);
ColumnInfo col3 = new ColumnInfo(xlsDoc, sheet);
col3.ColumnIndexEnd = 3;
col3.ColumnIndexStart = 3;
col3.Width = 21 * 269;
sheet.AddColumnInfo(col3);
//以下定义文档标题 副标题
int rint = 1;
cells.Merge(rint, rint, 1, 5);
Cell cell = cells.Add(rint, 1, "医保报销帐单");
cell.Font.Height = 16 * 20;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
rint++;
cells.Merge(rint, rint, 1, 5);
Cell cell2 = cells.Add(rint, 1, "( " + beginDate.ToShortDateString() + "至" + dtpEndDate.Value.ToShortDateString() + " )");
cell2.Font.Height = 12 * 20;
cell2.Font.FontName = "宋体";
cell2.HorizontalAlignment = HorizontalAlignments.Centered;
rint++;
FormatHead(cells.Add(rint, 1, "银行帐号"));
FormatHead(cells.Add(rint, 2, "金额"));
FormatHead(cells.Add(rint, 3, "姓名"));
FormatHead(cells.Add(rint, 4, "身份证号"));
FormatHead(cells.Add(rint, 5, "备注"));
foreach (MRS.Model.AccountBook account in accountBooks)
{
rint++;
PMS.BLL.MedicarePerson bll_mp = new PMS.BLL.MedicarePerson();
string bankAccount = bll_mp.GetMedicarePerson(account.YBH).BankAccount;
if (bankAccount != null)
{
FormatCellNumber(cells.Add(rint, 1, bankAccount));
FormatCellAmount(cells.Add(rint, 2, Convert.ToDouble(account.BXJE)));
FormatCellName(cells.Add(rint, 3, account.Name));
FormatCellNumber(cells.Add(rint, 4, account.IdentityCard));
FormatCellName(cells.Add(rint, 5, "报销"));
}
}
rint++;
FormatCellButtom(cells.Add(rint, 1, ""));
FormatCellButtom(cells.Add(rint, 2, ""));
FormatCellButtom(cells.Add(rint, 3, ""));
FormatCellButtom(cells.Add(rint, 4, ""));
FormatCellButtom(cells.Add(rint, 5, ""));
saveFileDialog1.DefaultExt = "xls";
saveFileDialog1.FileName = fileName.ToString();
saveFileDialog1.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string path = System.IO.Path.GetDirectoryName(saveFileDialog1.FileName);
try
{
xlsDoc.Save(path, true);
}
catch
{
MessageBox.Show("文件 \"" + System.IO.Path.GetFileName(saveFileDialog1.FileName) + "\" 正被其它程序打开，\n请关闭该文件，并重试！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show("无医保报销帐单!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// 将tables写入EXCEL文件，已预制格式，可覆盖同名文件
/// </summary>
/// <param name="file_path">绝对路径，可覆盖同名文件</param>
/// <param name="tables"></param>
public static void WriteAsXls(string file_path, System.Data.DataTable[] tables)
{
XlsDocument xls = new XlsDocument();
xls.FileName = System.IO.Path.GetFileName(file_path);
for (int i = 0; i < tables.Length; i++)
{
System.Data.DataTable table = tables[i];
if (table.Columns.Count > BIFF8.MaxCols)
{
throw new ApplicationException(string.Format("Table {0} has too many columns {1} to fit on Worksheet {2} with the given startCol {3}",
table.TableName, table.Columns.Count, BIFF8.MaxCols, 1));
}
if (table.Rows.Count > (BIFF8.MaxRows - 1))
{
throw new ApplicationException(string.Format("Table {0} has too many rows {1} to fit on Worksheet {2} with the given startRow {3}",
table.TableName, table.Rows.Count, (BIFF8.MaxRows - 1), 1));
}
Worksheet sheet = xls.Workbook.Worksheets.Add(table.TableName);
ColumnInfo ci = new ColumnInfo(xls, sheet);
ci.Width = (20 * 256);
ci.ColumnIndexEnd = (ushort)table.Rows.Count;
sheet.AddColumnInfo(ci);
int row = 1, col = 1;
foreach (System.Data.DataColumn dataColumn in table.Columns)
{
Cell cellHeader = sheet.Cells.Add(row, col++, dataColumn.ColumnName);
cellHeader.Font.Bold = true;
cellHeader.HorizontalAlignment = HorizontalAlignments.Centered;
cellHeader.Pattern = 1;
cellHeader.PatternColor = Colors.Silver;
cellHeader.UseBorder = true;
cellHeader.TopLineStyle = 1;
cellHeader.TopLineColor = Colors.Silver;
cellHeader.BottomLineStyle = 1;
cellHeader.BottomLineColor = Colors.Silver;
cellHeader.LeftLineStyle = 1;
cellHeader.LeftLineColor = Colors.Silver;
cellHeader.RightLineStyle = 1;
cellHeader.RightLineColor = Colors.Silver;
}
foreach (System.Data.DataRow dataRow in table.Rows)
{
row++;
col = 1;
foreach (object dataItem in dataRow.ItemArray)
{
object value = dataItem;
if (dataItem == DBNull.Value)
{
value = null;
}
if (dataRow.Table.Columns[col - 1].DataType == typeof(byte[]))
{
value = string.Format("[ByteArray({0})]", ((byte[])value).Length);
}
Cell cellbody = sheet.Cells.Add(row, col++, value);
}
}
}
xls.Save(System.IO.Path.GetDirectoryName(file_path), true);
}
protected void Page_Load(object sender, EventArgs e)
{
org.in2bits.MyXls.XlsDocument doc = new XlsDocument();
doc.FileName = "TestingAgain.xls";
for (int s = 1; s <= 5; s++)
{
string sheetName;
//if (this.IsPostBack == true)
//{
sheetName = Request.Form["txtSheet" + s].Replace(",", string.Empty);
//}
//else sheetName = string.Empty;
if (sheetName.Trim() == string.Empty)
{
continue;
}
int rowMin, rowCount, colMin, colCount;
try
{
rowMin = int.Parse(Request.Form["txtRowMin" + s]) + 1;
rowCount = int.Parse(Request.Form["txtRows" + s]);
colMin = int.Parse(Request.Form["txtColMin" + s]);
colCount = int.Parse(Request.Form["txtCols" + s]);
}
catch
{
continue;
}
if (rowCount > 5000)
{
rowCount = 5000;
}
if (rowCount < 0)
{
rowCount = 0;
}
if (rowMin < 1)
{
rowMin = 1;
}
if (rowMin > 32767)
{
rowMin = 32767;
}
if (colCount > 50)
{
colCount = 50;
}
if (colCount < 1)
{
colCount = 1;
}
if (colMin < 1)
{
colMin = 1;
}
if (colMin > 100)
{
colMin = 100;
}
if (sheetName.Length > 35)
{
sheetName = sheetName.Substring(0, 35);
}
Worksheet sheet = doc.Workbook.Worksheets.AddNamed(sheetName);
Cells cells = sheet.Cells;
Cell miCelda;
ColumnInfo cinfo;
cinfo = new ColumnInfo(doc, sheet);
cinfo.ColumnIndexStart = 1;
cinfo.ColumnIndexEnd = 1;
cinfo.Width = 200;
miCelda = cells.AddValueCell(1, 1, "TU ABUELA");
sheet.AddColumnInfo(cinfo);
for (int row = 0; row <= rowCount; row++)
{
if (row == 0)
{
for (int col = 1; col <= colCount; col++)
{
cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), "Fld" + col);
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), row + col);
}
}
}
}
doc.Send();
Response.Flush();
Response.End();
}
//数据的导入和导出
protected void ExportExcel(DataTable dt, string filename)
{
XlsDocument xls = new XlsDocument(); //新建一个xls文档
xls.FileName = filename; //设定文件名
string sheetName = "动车票价和定员表";
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格（cell）集合
////设置列的宽度
ColumnInfo colInfo = new ColumnInfo(xls, sheet);//生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = 0; //起始列为第一列,ColumnIndexStart是从0开始
colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
//列样式
XF cellXFColumnText = xls.NewXF();
cellXFColumnText.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXFColumnText.Font.FontName = "宋体"; //设定字体
cellXFColumnText.Font.Height = 10 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXFColumnText.Pattern = 1; //设定单元格填充风格。如果设定为0，则是纯色填充
cellXFColumnText.PatternBackgroundColor = Colors.Grey; //填充的底色
cellXFColumnText.PatternColor = Colors.Grey; //设定填充线条的颜色
cellXFColumnText.BottomLineStyle = 1; //设定边框底线为粗线
cellXFColumnText.BottomLineColor = Colors.Grey;
cellXFColumnText.TopLineStyle = 1; //设定边框底线为粗线
cellXFColumnText.TopLineColor = Colors.Grey;
cellXFColumnText.LeftLineStyle = 1;
cellXFColumnText.LeftLineColor = Colors.Grey;
cellXFColumnText.RightLineStyle = 1;
cellXFColumnText.RightLineColor = Colors.Grey;
cells.Add(1, 1, "序号", cellXFColumnText); //设定第一行，第一列单元格的值
cells.Add(1, 2, "动车类型", cellXFColumnText); //设定第一行，第二列单元格的值
cells.Add(1, 3, "动车类别", cellXFColumnText); //设定第一行，第三列单元格的值
cells.Add(1, 4, "一等座基本票价", cellXFColumnText); //设定第一行，第四列单元格的值
cells.Add(1, 5, "一等座定员", cellXFColumnText); //设定第一行，第五列单元格的值
cells.Add(1, 6, "二等座基本票价", cellXFColumnText);
cells.Add(1, 7, "二等座定员", cellXFColumnText);
cells.Add(1, 8, "动卧上铺基本票价", cellXFColumnText);
cells.Add(1, 9, "动卧下铺基本票价", cellXFColumnText);
cells.Add(1, 10, "动卧定员", cellXFColumnText);
cells.Add(1, 11, "商务座基本票价", cellXFColumnText);
cells.Add(1, 12, "商务座定员", cellXFColumnText);
//列值样式
XF cellXFColumnValue = xls.NewXF();
cellXFColumnValue.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cellXFColumnValue.Font.FontName = "宋体"; //设定字体
cellXFColumnValue.Font.Height = 10 * 20; //设定字大小（字体大小是以 1/20 point 为单位的）
cellXFColumnValue.UseBorder = true; //使用边框
cellXFColumnValue.BottomLineStyle = 1; //设定边框底线为粗线
cellXFColumnValue.BottomLineColor = Colors.Grey;
cellXFColumnValue.TopLineStyle = 1; //设定边框底线为粗线
cellXFColumnValue.TopLineColor = Colors.Grey;
cellXFColumnValue.LeftLineStyle = 1;
cellXFColumnValue.LeftLineColor = Colors.Grey;
cellXFColumnValue.RightLineStyle = 1;
cellXFColumnValue.RightLineColor = Colors.Grey;
int currentRow = 2;
foreach (DataRow dr in dt.Rows)
{
cells.Add(currentRow, 1, Convert.ToString(dr["id"]), cellXFColumnValue); //动车类型
cells.Add(currentRow, 2, Convert.ToString(dr["HIGHTRAINTYPE"]), cellXFColumnValue); //动车类型
cells.Add(currentRow, 3, Convert.ToString(dr["MILETYPE"]), cellXFColumnValue); //里程
cells.Add(currentRow, 4, Convert.ToString(dr["RATE1"]), cellXFColumnValue); //一等座票价
cells.Add(currentRow, 5, Convert.ToString(dr["PCOUNT1"]), cellXFColumnValue);
cells.Add(currentRow, 6, Convert.ToString(dr["RATE2"]), cellXFColumnValue);
cells.Add(currentRow, 7, Convert.ToString(dr["PCOUNT2"]), cellXFColumnValue);
cells.Add(currentRow, 8, Convert.ToString(dr["RATE3"]), cellXFColumnValue);
cells.Add(currentRow, 9, Convert.ToString(dr["RATE31"]), cellXFColumnValue);
cells.Add(currentRow, 10, Convert.ToString(dr["PCOUNT3"]), cellXFColumnValue);
cells.Add(currentRow, 11, Convert.ToString(dr["RATE4"]), cellXFColumnValue);
cells.Add(currentRow, 12, Convert.ToString(dr["PCOUNT4"]), cellXFColumnValue);
currentRow++;
}
xls.Send(XlsDocument.SendMethods.Inline);
}
/// <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>
/// 把相关数据导出到Excel文件中
/// </summary>
/// <param name="dtSource">数据源内容</param>
/// <param name="strFileName">导出的Excel文件名</param>
public static void Export(DataTable dtSource, string strFileName, string SheetName = "Sheet1", Int32 startRow = 0, Int32 startColumn = 0)
{
XlsDocument xls = new XlsDocument();
xls.FileName = DateTimeHelper.GetServerDateTime2().ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo);
xls.SummaryInformation.Author = "吴建明"; //填加xls文件作者信息
xls.SummaryInformation.NameOfCreatingApplication = "jCodes 项目管理"; //填加xls文件创建程序信息
xls.SummaryInformation.LastSavedBy = "吴建明"; //填加xls文件最后保存者信息
xls.SummaryInformation.Comments = "吴建明添加的项目信息"; //填加xls文件作者信息
xls.SummaryInformation.Title = "项目管理信息"; //填加xls文件标题信息
xls.SummaryInformation.Subject = "jCodes项目管理信息"; //填加文件主题信息
xls.DocumentSummaryInformation.Company = "jCodes.cn"; //填加文件公司信息
Worksheet sheet = xls.Workbook.Worksheets.Add(SheetName); //状态栏标题名称
Cells cells = sheet.Cells;
ushort[] colArr = new ushort[dtSource.Columns.Count]; // 保存最大的字节数用于调整最佳宽度
Int32 colIdx = 0;
foreach (DataColumn col in dtSource.Columns)
{
Cell cell = cells.Add(1 + startRow, col.Ordinal + 1 + startColumn, col.ColumnName);
cell.Font.FontName = "宋体"; // 字体
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.UseBorder = true; // 使用边框
cell.TopLineStyle = 2; // 上边框样式
cell.TopLineColor = Colors.Black; // 上边框颜色
cell.LeftLineStyle = 2; // 左边框样式
cell.LeftLineColor = Colors.Black; // 左边框颜色
cell.RightLineStyle = 2; // 右边框样式
cell.RightLineColor = Colors.Black; // 右边框颜色
cell.BottomLineStyle = 2;
cell.BottomLineColor = Colors.Black;
cell.Pattern = 1; // 单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色
cell.PatternColor = Colors.Default2B; // ffff99
cell.PatternBackgroundColor = Colors.Default2B; // 填充的底色
cell.Font.Bold = true; //字体为粗体
// 保存数据对象
colArr[colIdx++] = (ushort)(System.Text.Encoding.Default.GetBytes(col.ColumnName.ToCharArray()).Length + 2); // 默认再加2个字节
}
sheet.Rows[(ushort)(startRow + 1)].RowHeight = 18 * 20;
#region 填充内容
XF dateStyle = xls.NewXF();
dateStyle.Format = "yyyy-mm-dd";
for (int i = 0; i < dtSource.Rows.Count; i++)
{
colIdx = 0;
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int rowIndex = i + 2;
int colIndex = j + 1;
string drValue = dtSource.Rows[i][j].ToString();
Cell cell = null;
switch (dtSource.Rows[i][j].GetType().ToString())
{
case "System.String": //字符串类型
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, drValue);
if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(drValue.ToCharArray()).Length + 2))
{
colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(drValue.ToCharArray()).Length + 2);
}
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, dateV, dateStyle);
if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(dateV.ToString().ToCharArray()).Length + 2))
{
colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(dateV.ToString().ToCharArray()).Length + 2);
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, boolV);
if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(boolV.ToString().ToCharArray()).Length + 2))
{
colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(boolV.ToString().ToCharArray()).Length + 2);
}
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, intV);
if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(intV.ToString().ToCharArray()).Length + 2))
{
colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(intV.ToString().ToCharArray()).Length + 2);
}
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, doubV);
if ((ushort)colArr[colIdx] < (ushort)(System.Text.Encoding.Default.GetBytes(doubV.ToString().ToCharArray()).Length + 2))
{
colArr[colIdx] = (ushort)(System.Text.Encoding.Default.GetBytes(doubV.ToString().ToCharArray()).Length + 2);
}
break;
case "System.DBNull": //空值处理
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, null);
break;
default:
cell = cells.Add(rowIndex + startColumn, colIndex + startColumn, null);
break;
}
// 下一列
colIdx++;
if (null != cell)
{
cell.UseBorder = true; // 使用边框
cell.TopLineStyle = 1; // 上边框样式
cell.TopLineColor = Colors.Black; // 上边框颜色
cell.LeftLineStyle = 1; // 左边框样式
cell.LeftLineColor = Colors.Black; // 左边框颜色
cell.RightLineStyle = 1; // 右边框样式
cell.RightLineColor = Colors.Black; // 右边框颜色
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.Pattern = 1; // 单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色
cell.PatternColor = Colors.White; // 白色
cell.PatternBackgroundColor = Colors.White; // 填充的底色
}
}
}
#endregion
#region 宽度最优
colIdx = 0;
foreach (DataColumn c in dtSource.Columns)
{
ColumnInfo col = new ColumnInfo(xls, sheet); // 列对象
col.ColumnIndexStart = (ushort)(startColumn + colIdx); // 起始列为第1列，索引从0开始
col.ColumnIndexEnd = (ushort)(startColumn + colIdx); // 终止列为第1列，索引从0开始
col.Width = (ushort)((ushort)colArr[colIdx] * 256); // 列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(col); // 把格式附加到sheet页上
colIdx++;
}
#endregion
xls.FileName = strFileName;
xls.Save(true);
}
/// <summary>
/// 导出申请单excel，包括我的申请和我的审批
/// </summary>
/// <param name="fc"></param>
public void ExporDRData(string queryJson, string pageType)
{
SearchMyApplyParams p = JsonConvert.DeserializeObject <SearchMyApplyParams>(queryJson);
p.account = currentAccount;
p.userId = currentUser.userId;
p.userName = currentUser.userName;
var result = new List <CheckApplyListModel>();
string fileName = "";
if (pageType.Equals("apply"))
{
fileName = "我的送货申请_";
result = new DRSv().SearchMyApplyList(p, canCheckAll).ToList();
}
else if (pageType.Equals("audit"))
{
fileName = "审核送货申请_";
result = new DRSv().SearchMyAuditList(p, canCheckAll).ToList();
}
ushort[] colWidth = new ushort[] { 16, 18, 12, 16, 10, 24,
32, 16, 16, 12, 18, 18, 16,
16, 16, 16, 16, 18, 16, 18, 16 };
string[] colName = new string[] { "发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
"规格型号", "订单数量", "申请数量", "单位", "物料编码", "备注", "订单类型",
"采购方式", "订料员", "采购员", "贸易类型", "PR单号", "采购日期", "入库单号", "入库日期" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = fileName + DateTime.Now.ToString("MMddHHmmss");
Worksheet sheet = xls.Workbook.Worksheets.Add("申请列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in result)
{
colIndex = 1;
//"发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
//"规格型号", "订单数量", "申请数量", "单位", "物料编码","订单类型",
//"采购方式","订料员","采购员","贸易类型","PR单号","采购日期","入库单号","入库日期"
cells.Add(++rowIndex, colIndex, ((DateTime)d.sendDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.billNo);
cells.Add(rowIndex, ++colIndex, d.pStatus);
cells.Add(rowIndex, ++colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.poQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.sendQty));
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.comment);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.inStockBillNo);
cells.Add(rowIndex, ++colIndex, d.inStockBillDate == null?"":((DateTime)d.inStockBillDate).ToString("yyyy-MM-dd"));
}
xls.Send();
WLog("导出申请单Excel", fileName + ":" + queryJson);
}
/// <summary>
/// 导出生成excel文件
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="fileName">文件名.xls</param>
/// <param name="longColumnsIndex">需要设置长宽度的列的集合eg."1,2,3,4"</param>
public static void CreateXls(DataTable dt, string fileName, string longColumnsIndex)
{
XlsDocument xls = new XlsDocument();
if (System.Web.HttpContext.Current.Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
xls.FileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
else
{
xls.FileName = fileName;
}
int rowIndex = 1;
int colIndex = 0;
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");//状态栏标题名称
Cells cells = sheet.Cells;
//设置整体列宽带
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 22;
colInfo.Width = 14 * 256;
sheet.AddColumnInfo(colInfo);
if (longColumnsIndex.Length > 0)
{
//单独设置需要长宽度的列
string[] longIndexs = longColumnsIndex.Split(',');
if (longIndexs.Length > 0)
{
//遍历数组，设置每一列的宽度
for (int i = 0; i < longIndexs.Length; i++)
{
ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
colInfo1.ColumnIndexStart = ushort.Parse(longIndexs[i]);
colInfo1.ColumnIndexEnd = ushort.Parse(longIndexs[i]);
colInfo1.Width = 36 * 256;
sheet.AddColumnInfo(colInfo1);
}
}
}
//设置样式
XF xf = xls.NewXF();
xf.UseProtection = false;
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.TextWrapRight = true;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
//
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(1, colIndex, col.ColumnName, xf);
}
//sheet.Rows[1].RowHeight = 24 * 20;
//填充数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString(), xf); //转换为数字型
//如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = false; //字体为粗体
}
//设置行高
//sheet.Rows[(ushort)rowIndex].RowHeight = 24 * 20;
}
xls.Send(XlsDocument.SendMethods.Attachment);
}
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);
}
/// <summary>
/// 导出excel通用方法
/// </summary>
/// <param name="myData">导出的数据</param>
private void ExportExcel(List <ExcelData> myData)
{
//列名：
string[] colName = new string[] { "审核结果", "流水号", "备料日期", "客户型号", "版本号", "产品类别", "客户编码", "客户名称", "终端客户编码", "终端客户名称",
"产品代码", "产品名称", "产品型号", "备料数量(粒)", "营业员", "产品用途", "订料良率", "产品类型", "表面处理", "是否半孔板",
"是否出样", "贸易类型", "事业部", "备料类型", "协议号", "制单人", "备料项目", "备注", "消耗计划" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = string.Format("电子备料单_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
Worksheet sheet = xls.Workbook.Worksheets.Add("备料信息列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colName.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(16 * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
foreach (var d in myData)
{
colIndex = 1;
// "审核结果","流水号","备料日期","客户型号","版本号","产品类别","客户编码","客户名称","终端客户编码","终端客户名称",
// "产品代码","产品名称","产品型号","备料数量(粒)","营业员","产品用途","订料良率","产品类型","表面处理","是否半孔板",
// "是否出样","贸易类型","事业部","备料类型","协议号","制单人","备料项目","备注","消耗计划"
cells.Add(++rowIndex, colIndex, d.auditStatus);
cells.Add(rowIndex, ++colIndex, d.h.sys_no);
cells.Add(rowIndex, ++colIndex, d.h.bill_date.ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.h.customer_pn);
cells.Add(rowIndex, ++colIndex, d.h.version_no);
cells.Add(rowIndex, ++colIndex, d.h.product_type);
cells.Add(rowIndex, ++colIndex, d.h.customer_no);
cells.Add(rowIndex, ++colIndex, d.h.customer_name);
cells.Add(rowIndex, ++colIndex, d.h.zz_customer_no);
cells.Add(rowIndex, ++colIndex, d.h.zz_customer_name);
cells.Add(rowIndex, ++colIndex, d.h.product_no);
cells.Add(rowIndex, ++colIndex, d.h.product_name);
cells.Add(rowIndex, ++colIndex, d.h.product_model);
cells.Add(rowIndex, ++colIndex, d.h.bl_qty);
cells.Add(rowIndex, ++colIndex, d.h.clerk_name);
cells.Add(rowIndex, ++colIndex, d.h.usage);
cells.Add(rowIndex, ++colIndex, d.h.good_percent);
cells.Add(rowIndex, ++colIndex, d.h.product_classification);
cells.Add(rowIndex, ++colIndex, d.h.surface_type);
cells.Add(rowIndex, ++colIndex, d.h.is_half_hole);
cells.Add(rowIndex, ++colIndex, d.h.is_make_sample);
cells.Add(rowIndex, ++colIndex, d.h.trade_type);
cells.Add(rowIndex, ++colIndex, d.h.bus_name);
cells.Add(rowIndex, ++colIndex, d.h.bl_type);
cells.Add(rowIndex, ++colIndex, d.h.protocol_no);
cells.Add(rowIndex, ++colIndex, d.h.applier_name);
cells.Add(rowIndex, ++colIndex, d.h.bl_project);
cells.Add(rowIndex, ++colIndex, d.h.comment);
cells.Add(rowIndex, ++colIndex, d.h.bl_plan);
}
xls.Send();
}
/// <summary>
/// 将DataTable导出为Xls文件
/// neo 2010-12-02
/// </summary>
/// <param name="ExcelData"></param>
/// <param name="GridView1"></param>
/// <param name="sExcelDir"></param>
/// <param name="sSheetName"></param>
/// <returns>返回生成Excel文件本地路径</returns>
public static string SaveAsExcel(DataTable ExcelData, GridView GridView1, string sExcelDir, string sSheetName)
{
if (ExcelData == null)
{
return(string.Empty);
}
NameValueCollection ExcelColumns = new NameValueCollection();
foreach (DataControlField GridColumn in GridView1.Columns)
{
// 隐藏列 不导出
if (GridColumn.Visible == false)
{
continue;
}
// 只导出BoundField列
if (GridColumn.GetType().FullName != "System.Web.UI.WebControls.BoundField")
{
continue;
}
// 如果没有列名 不导出
if (GridColumn.HeaderText == string.Empty)
{
continue;
}
BoundField BoundField1 = GridColumn as BoundField;
ExcelColumns.Add(BoundField1.HeaderText, BoundField1.DataField);
}
// 生成随机唯一文件名
string sExcelFileName = Guid.NewGuid().ToString() + ".xls";
// 创建Excel文档
XlsDocument ExcelDoc = new XlsDocument();
ExcelDoc.FileName = sExcelFileName;
// 添加Sheet
Worksheet Sheet = ExcelDoc.Workbook.Worksheets.Add(sSheetName);
//Sheet.Name = sSheetName;
// 添加第一行列名
ColumnInfo ColumnInfo1 = new ColumnInfo(ExcelDoc, Sheet);
ColumnInfo1.ColumnIndexStart = 0;
ColumnInfo1.ColumnIndexEnd = Convert.ToUInt16(ExcelColumns.Count - 1);
ColumnInfo1.Width = 25 * 150;
Sheet.AddColumnInfo(ColumnInfo1);
// 生成第一行Excel列
int i = 1;
foreach (string sHeaderText in ExcelColumns.AllKeys)
{
Cell CellObj = Sheet.Cells.Add(1, i, sHeaderText);
CellObj.Font.Weight = FontWeight.Bold;
CellObj.Pattern = 1;
CellObj.PatternColor = Colors.Silver;
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
i++;
}
int j = 2;
foreach (DataRow RowObj in ExcelData.Rows)
{
int m = 1;
foreach (string sHeaderText in ExcelColumns.AllKeys)
{
// 获取对应的DataField
string sDataField = ExcelColumns[sHeaderText];
object oValue = RowObj[sDataField];
if (oValue.GetType().FullName == "System.Byte")
{
oValue = Convert.ToInt16(oValue);
}
else if (oValue.GetType().FullName == "System.DBNull")
{
oValue = string.Empty;
}
else if (oValue.GetType().FullName == "System.String")
{
oValue = oValue.ToString();
}
else if (oValue.GetType().FullName == "System.DateTime")
{
oValue = Convert.ToDateTime(oValue).ToString();
}
Cell CellObj = Sheet.Cells.Add(j, m, oValue);
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
m++;
}
j++;
}
string sPath = sExcelDir + sExcelFileName;
if (File.Exists(sPath) == true)
{
File.Delete(sPath);
}
ExcelDoc.Save(sExcelDir, true);
return(sPath);
}
//导出数据到Excel
void butExportData_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument(); //新建一个xls文档
xls.FileName = "train.xls"; //设定文件名
List <String> lineNameList = BusinessRule.Line.GetTrainLindName();
JTable tab1 = new WebFrame.Data.JTable();
tab1.TableName = "linestationview";
foreach (String m in lineNameList)
{
string sheetName = m;
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"chc 实例"的sheet页
Cells cells = sheet.Cells; //Cells实例是sheet页中单元格（cell）集合
////设置列的宽度
#region 设置列的宽度
ColumnInfo colInfo = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo.ColumnIndexStart = 0; //起始列为第一列,ColumnIndexStart是从0开始
colInfo.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo1 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo1.ColumnIndexStart = 1; //起始列为第一列,ColumnIndexStart是从0开始
colInfo1.Width = 20 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo1); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo2 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo2.ColumnIndexStart = 2; //起始列为第一列,ColumnIndexStart是从0开始
colInfo2.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo2); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo3 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo3.ColumnIndexStart = 3; //起始列为第一列,ColumnIndexStart是从0开始
colInfo3.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo3); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo4 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo4.ColumnIndexStart = 4; //起始列为第一列,ColumnIndexStart是从0开始
colInfo4.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo4); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo5 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo5.ColumnIndexStart = 5; //起始列为第一列,ColumnIndexStart是从0开始
colInfo5.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo5); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo6 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo6.ColumnIndexStart = 6; //起始列为第一列,ColumnIndexStart是从0开始
colInfo6.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo6); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo7 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo7.ColumnIndexStart = 7; //起始列为第一列,ColumnIndexStart是从0开始
colInfo7.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo7); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
ColumnInfo colInfo8 = new ColumnInfo(xls, sheet); //生成列格式对象
//设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base)
colInfo8.ColumnIndexStart = 8; //起始列为第一列,ColumnIndexStart是从0开始
colInfo8.Width = 15 * 256; //列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(colInfo8); //把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页）
#endregion
XF cellXFColumnText = xls.NewXF();
cellXFColumnText.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中
cells.Add(1, 1, "线名代码", cellXFColumnText); //设定第一行，第一列单元格的值
cells.Add(1, 2, "线名", cellXFColumnText); //设定第一行，第二列单元格的值
cells.Add(1, 3, "站名代码", cellXFColumnText); //设定第一行，第三列单元格的值
cells.Add(1, 4, "始发站", cellXFColumnText); //设定第一行，第四列单元格的值
cells.Add(1, 5, "到达站", cellXFColumnText); //设定第一行，第四列单元格的值
cells.Add(1, 6, "里程", cellXFColumnText);
cells.Add(1, 7, "局内", cellXFColumnText);
cells.Add(1, 8, "电气化", cellXFColumnText);
cells.Add(1, 9, "轮渡标志", cellXFColumnText);
int currentRow = 2;
DataTable dt = BusinessRule.Line.GetTrainLindData(tab1, m);
foreach (DataRow dr in dt.Rows)
{
String jnFlag = dr["jnflag"].ToString().Trim();
if (jnFlag != String.Empty)
{
jnFlag = "是";
}
String dqh = dr["dqh"].ToString().Trim();
if (dqh != String.Empty)
{
dqh = "是";
}
String shipFlag = dr["shipflag"].ToString().Trim();
if (shipFlag != String.Empty)
{
shipFlag = "是";
}
cells.Add(currentRow, 1, Convert.ToString(dr["lineid"]), cellXFColumnText);
cells.Add(currentRow, 2, Convert.ToString(dr["linename"]), cellXFColumnText);
cells.Add(currentRow, 3, Convert.ToString(""), cellXFColumnText);
cells.Add(currentRow, 4, Convert.ToString(dr["Astation"]), cellXFColumnText);
cells.Add(currentRow, 5, Convert.ToString(dr["bstation"]), cellXFColumnText);
cells.Add(currentRow, 6, Convert.ToString(dr["miles"]), cellXFColumnText);
cells.Add(currentRow, 7, jnFlag, cellXFColumnText);
cells.Add(currentRow, 8, dqh, cellXFColumnText);
cells.Add(currentRow, 9, shipFlag, cellXFColumnText);
currentRow++;
}
}
tab1.Close();
xls.Send(XlsDocument.SendMethods.Inline);
}
/// <summary>
/// 直接导出Excel
/// </summary>
/// <param name="dataTable">数据</param>
/// <param name="filePathName">文件保存路径（E:\\excelname.xls）</param>
/// <param name="sheetName">表单名称（表单1）</param>
/// <param name="headers">头部显示的文字（编号，名称，...），如果为空则取dataTable的头</param>
/// <returns>返回成功与否</returns>
public static bool Export(DataTable dataTable, string filePathName, string sheetName, params string[] headers)
{
try
{
if (dataTable == null)
{
return(false);
}
if (filePathName == null)
{
return(false);
}
if (sheetName == null || sheetName.Trim() == "")
{
sheetName = "Sheet";
}
if (headers == null)
{
headers = new string[0];
}
string path = Path.GetDirectoryName(filePathName);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
List <string> headerList = headers.ToList();
if (headers.Length < dataTable.Columns.Count)
{
for (int i = headers.Length; i < dataTable.Columns.Count; i++)
{
headerList.Add(dataTable.Columns[i].ColumnName);
}
}
XlsDocument xls = new XlsDocument();//新建Excel
int startIndex = 0;
int endIndex = startIndex + 65533;
if (dataTable.Rows.Count < endIndex + 1)
{
endIndex = dataTable.Rows.Count - 1;
}
string sheetText = sheetName;
int count = 0;
do
{
//新建一个表单
Worksheet ws = xls.Workbook.Worksheets.Add(sheetText);
//开始到出数据
ColumnInfo columnInfo = new ColumnInfo(xls, ws);//头
columnInfo.ColumnIndexStart = 0;
columnInfo.ColumnIndexEnd = (ushort)(headerList.Count - 1);
columnInfo.Width = 21 * 256;
ws.AddColumnInfo(columnInfo);
for (ushort i = 1; i <= headerList.Count; i++)
{
ws.Cells.Add((ushort)1, i, headerList[i - 1]);
ws.Rows[1].GetCell(i).Font.Bold = true;
ws.Rows[1].GetCell(i).Font.Height = 13 * 20;
ws.Rows[1].GetCell(i).TopLineStyle = 1;
ws.Rows[1].GetCell(i).BottomLineStyle = 2;
ws.Rows[1].GetCell(i).LeftLineStyle = 1;
ws.Rows[1].GetCell(i).RightLineStyle = 1;
ws.Rows[1].GetCell(i).Pattern = 1;
ws.Rows[1].GetCell(i).PatternColor = org.in2bits.MyXls.Colors.Default1F;
}
for (int i = startIndex; i <= endIndex; i++)
{
object[] objs = dataTable.Rows[i].ItemArray;
for (ushort j = 1; j <= objs.Length; j++)
{
if (objs[j - 1] == DBNull.Value)
{
objs[j - 1] = "";
}
else if (objs[j - 1].GetType().Equals(typeof(DateTime)))
{
objs[j - 1] = ((DateTime)objs[j - 1]).ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
Type type = objs[j - 1].GetType();
if (type.Equals(typeof(int)) || type.Equals(typeof(Int64)) || type.Equals(typeof(float)) || type.Equals(typeof(double)) || type.Equals(typeof(decimal)) || type.Equals(typeof(byte)) || type.Equals(typeof(ushort)) || type.Equals(typeof(uint)) || type.Equals(typeof(ulong)))
{
objs[j - 1] = "'" + objs[j - 1];
}
}
ws.Cells.Add((ushort)(i + 2 - startIndex), j, objs[j - 1]);
ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).Font.Height = 13 * 20;
ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).TopLineStyle = 1;
ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).BottomLineStyle = 1;
ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).LeftLineStyle = 1;
ws.Rows[(ushort)(i + 2 - startIndex)].GetCell(j).RightLineStyle = 1;
}
}
//--结束导出数据
//下一个表单计算
startIndex = endIndex + 1;
if (startIndex >= dataTable.Rows.Count)
{
break;
}
else
{
endIndex = startIndex + 65533;
if (dataTable.Rows.Count < endIndex + 1)
{
endIndex = dataTable.Rows.Count - 1;
}
sheetText = sheetName + "-" + (++count);
}
} while (true);
xls.Save(filePathName, true);
xls = null;
return(true);
}
catch
{
return(false);
}
}