mes-client/UAS_MES_LGDZ/DataOperate/ExcelHandler.cs

using System.IO;
using System.Data;
using System;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Eval;
using System.Text;
using NPOI.SS.Util;
using System.Drawing;
using System.Collections.Generic;
using Seagull.BarTender.Print;
using DevExpress.XtraPrinting.Native.LayoutAdjustment;
using DevExpress.XtraExport.Implementation;
using NPOI.HSSF.Record.CF;
using UAS_MES_NEW.Entity;
using DevExpress.Schedule;
using System.Runtime.Remoting.Metadata.W3cXsd2001;

namespace UAS_MES_NEW.DataOperate
{
    class ExcelHandler
    {
        DataHelper dh = new DataHelper();
        /// <summary>
        /// 导出Excel,返回文件在客户端的路径
        /// </summary>
        public string ExportExcel(DataTable dt, string FolderPath)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel(dt);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = FolderPath;
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }

        public string ExportExcel_LIANGAN(DataTable dt, string FolderPath, string custname, string spec, string bomversion, string workdate, string softname, string filecode, string releasedate, string tableab, string ecn, string machinename, string mademan)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel_LIANGAN(dt, FolderPath, custname, spec, bomversion, workdate, softname, filecode, releasedate, tableab, ecn, machinename, mademan);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = FolderPath;
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }

        public MemoryStream DataTableToExcel_LIANGAN(DataTable DataTable, string FolderPath, string custname, string spec, string bomversion, string workdate, string softname, string filecode, string releasedate, string tableab, string ecn, string machinename, string mademan)
        {
            //创建内存流
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();
            ICellStyle style = book.CreateCellStyle();
            style.BorderTop = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderBottom = BorderStyle.THIN;
            //Excel中的Sheet
            //获取行数量和列数量
            int rowNum = DataTable.Rows.Count;
            int columnNum = DataTable.Columns.Count;
            //设置列的宽度，根据首行的列的内容的长度来设置
            string SheetName = "";
            //表格数据游标
            int DataRowCount = 8;
            string sumqty = "";
            int endrow = 0;
            for (int i = 0; i < 10; i++)
            {
                if (sumqty == "")
                    sumqty = DataTable.Rows[i]["sumqty"].ToString();
            }
            List<int> id=new List<int>();
            for (int i = 0; i < DataTable.Rows.Count; i++)
            {
                if (DataTable.Rows[i]["STF_MACHINE"].ToString() != "" && DataTable.Rows[i]["STF_MACHINE"].ToString() != "***" && i != DataTable.Rows.Count - 1)
                {
                    Console.WriteLine(DataTable.Rows[i]["STF_MACHINE"].ToString());
                    Console.WriteLine(DataTable.Rows[i + 1]["STF_MACHINE"].ToString());
                    ISheet sheet1 = book.CreateSheet(DataTable.Rows[i]["STF_MACHINE"].ToString() + "-" + DataTable.Rows[i + 1]["STF_MACHINE"].ToString().Substring(0, 1));
                    SheetName = DataTable.Rows[i]["STF_MACHINE"].ToString() + "-" + DataTable.Rows[i + 1]["STF_MACHINE"].ToString().Substring(0, 1);
                    IRow row1 = sheet1.CreateRow(0);
                    row1.CreateCell(0); row1.CreateCell(1); row1.Cells[0].SetCellValue("                                       SMT程式料表     ");
                    row1.Cells[0].CellStyle = style; row1.Cells[1].CellStyle = style;
                    row1.CreateCell(2); row1.CreateCell(3);

                    row1.Cells[2].CellStyle = style;
                    row1.Cells[2].CellStyle = style; row1.Cells[3].CellStyle = style;
                    row1.CreateCell(4); row1.CreateCell(5); row1.Cells[4].CellStyle = style;
                    row1.Cells[4].CellStyle = style; row1.Cells[5].CellStyle = style;
                    row1.CreateCell(6); row1.CreateCell(7); row1.Cells[6].CellStyle = style;
                    row1.Cells[6].CellStyle = style; row1.Cells[7].CellStyle = style;
                    row1.Cells[6].SetCellValue("点数:" + sumqty);
                    //标题
                    CellRangeAddress cellRangeAddress_first = new CellRangeAddress(0, 0, 0, 5);
                    sheet1.AddMergedRegion(cellRangeAddress_first);
                    //点数
                    CellRangeAddress cellRangeAddress_points = new CellRangeAddress(0, 0, 6, 7);
                    sheet1.AddMergedRegion(cellRangeAddress_points);
                    //客户抬头
                    IRow row2 = sheet1.CreateRow(1);
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 0, 1);
                    CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, 2, 3);
                    CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 4, 5);
                    CellRangeAddress cellRangeAddress3 = new CellRangeAddress(1, 1, 6, 7);
                    sheet1.AddMergedRegion(cellRangeAddress);
                    sheet1.AddMergedRegion(cellRangeAddress1);
                    sheet1.AddMergedRegion(cellRangeAddress2);
                    sheet1.AddMergedRegion(cellRangeAddress3);
                    row2.CreateCell(0); row2.CreateCell(1); row2.Cells[0].SetCellValue("客户名称:");
                    row2.Cells[0].CellStyle = style; row2.Cells[1].CellStyle = style;
                    row2.CreateCell(2); row2.CreateCell(3); row2.Cells[2].SetCellValue(custname); row2.Cells[2].CellStyle = style;
                    row2.Cells[2].CellStyle = style; row2.Cells[3].CellStyle = style;
                    row2.CreateCell(4); row2.CreateCell(5); row2.Cells[4].SetCellValue("文件编号:"+ filecode); row2.Cells[4].CellStyle = style;
                    row2.Cells[4].CellStyle = style; row2.Cells[5].CellStyle = style;
                    row2.CreateCell(6); row2.CreateCell(7); row2.Cells[6].SetCellValue("发行日期:" + releasedate); row2.Cells[6].CellStyle = style;
                    row2.Cells[6].CellStyle = style; row2.Cells[7].CellStyle = style;

                    //产品型号
                    IRow row3 = sheet1.CreateRow(2);
                    CellRangeAddress cellRangeAddress4 = new CellRangeAddress(2, 2, 0, 1);
                    CellRangeAddress cellRangeAddress5 = new CellRangeAddress(2, 2, 2, 3);
                    CellRangeAddress cellRangeAddress6 = new CellRangeAddress(2, 2, 4, 5);
                    CellRangeAddress cellRangeAddress7 = new CellRangeAddress(2, 2, 6, 7);
                    sheet1.AddMergedRegion(cellRangeAddress4);
                    sheet1.AddMergedRegion(cellRangeAddress5);
                    sheet1.AddMergedRegion(cellRangeAddress6);
                    sheet1.AddMergedRegion(cellRangeAddress7);
                    row3.CreateCell(0); row3.CreateCell(1); row3.Cells[0].SetCellValue("产品型号:");
                    row3.Cells[0].CellStyle = style; row3.Cells[1].CellStyle = style;
                    row3.CreateCell(2); row3.CreateCell(3); row3.Cells[2].SetCellValue(spec);
                    row3.Cells[2].CellStyle = style; row3.Cells[3].CellStyle = style;
                    row3.CreateCell(4); row3.CreateCell(5); row3.Cells[4].SetCellValue("BOM编号:"+bomversion);
                    row3.Cells[4].CellStyle = style; row3.Cells[5].CellStyle = style;
                    row3.CreateCell(6); row3.CreateCell(7); row3.Cells[6].SetCellValue("生效日期:" + workdate);
                    row3.Cells[6].CellStyle = style; row3.Cells[7].CellStyle = style;
                    //程序名称
                    IRow row4 = sheet1.CreateRow(3);
                    CellRangeAddress cellRangeAddress8 = new CellRangeAddress(3, 3, 0, 1);
                    CellRangeAddress cellRangeAddress9 = new CellRangeAddress(3, 3, 2, 3);
                    CellRangeAddress cellRangeAddress10 = new CellRangeAddress(3, 3, 4, 5);
                    sheet1.AddMergedRegion(cellRangeAddress8);
                    sheet1.AddMergedRegion(cellRangeAddress9);
                    sheet1.AddMergedRegion(cellRangeAddress10);
                    row4.CreateCell(0); row4.CreateCell(1); row4.Cells[0].SetCellValue("程序名称");
                    row4.Cells[0].CellStyle = style; row4.Cells[1].CellStyle = style;
                    row4.CreateCell(2); row4.CreateCell(3); row4.Cells[2].SetCellValue(DataTable.Rows[i]["STF_CODE"].ToString() + "");
                    row4.Cells[2].CellStyle = style; row4.Cells[3].CellStyle = style;
                    row4.CreateCell(4); row4.CreateCell(5); row4.Cells[4].SetCellValue("面别:    "+ tableab + "");
                    row4.Cells[4].CellStyle = style; row4.Cells[5].CellStyle = style;
                    row4.CreateCell(6); row4.CreateCell(7); row4.Cells[6].SetCellValue("ECN记录:" + ecn);
                    row4.Cells[6].CellStyle = style; row4.Cells[7].CellStyle = style;
                    //机器名称
                    IRow row5 = sheet1.CreateRow(4);
                    CellRangeAddress cellRangeAddress14 = new CellRangeAddress(4, 4, 0, 1);
                    CellRangeAddress cellRangeAddress15 = new CellRangeAddress(4, 4, 2, 3);
                    CellRangeAddress cellRangeAddress16 = new CellRangeAddress(4, 4, 4, 5);
                    CellRangeAddress cellRangeAddress20 = new CellRangeAddress(4, 5, 6, 7);
                    sheet1.AddMergedRegion(cellRangeAddress14);
                    sheet1.AddMergedRegion(cellRangeAddress15);
                    sheet1.AddMergedRegion(cellRangeAddress16);
                    sheet1.AddMergedRegion(cellRangeAddress20);
                    row5.CreateCell(0); row5.CreateCell(1); row5.Cells[0].SetCellValue("机器名称");
                    row5.Cells[0].CellStyle = style; row5.Cells[1].CellStyle = style;
                    row5.CreateCell(2); row5.CreateCell(3); row5.Cells[2].SetCellValue(machinename);
                    row5.Cells[2].CellStyle = style; row5.Cells[3].CellStyle = style;
                    row5.CreateCell(4); row5.CreateCell(5); row5.Cells[4].SetCellValue("QA确认:");
                    row5.Cells[4].CellStyle = style; row5.Cells[5].CellStyle = style;
                    row5.CreateCell(6); row5.CreateCell(7); row5.Cells[6].SetCellValue("文控签章:");
                    row5.Cells[6].CellStyle = style; row5.Cells[7].CellStyle = style;

                    //制作
                    IRow row6 = sheet1.CreateRow(5);
                    CellRangeAddress cellRangeAddress17 = new CellRangeAddress(5, 5, 0, 1);
                    CellRangeAddress cellRangeAddress18 = new CellRangeAddress(5, 5, 2, 3);
                    CellRangeAddress cellRangeAddress19 = new CellRangeAddress(5, 5, 4, 5);
                    sheet1.AddMergedRegion(cellRangeAddress17);
                    sheet1.AddMergedRegion(cellRangeAddress18);
                    sheet1.AddMergedRegion(cellRangeAddress19);
                    row6.CreateCell(0); row6.CreateCell(1); row6.Cells[0].SetCellValue("制作:"+ mademan);
                    row6.Cells[0].CellStyle = style; row6.Cells[1].CellStyle = style;
                    row6.CreateCell(2); row6.CreateCell(3); row6.Cells[2].SetCellValue(User.CurrentStepName);
                    row6.Cells[2].CellStyle = style; row6.Cells[3].CellStyle = style;
                    row6.CreateCell(4); row6.CreateCell(5); row6.Cells[4].SetCellValue("QA审核:");
                    row6.Cells[4].CellStyle = style; row6.Cells[5].CellStyle = style;
                    //制作
                    IRow row7 = sheet1.CreateRow(6);
                    CellRangeAddress cellRangeAddress21 = new CellRangeAddress(6, 6, 0, 7);
                    sheet1.AddMergedRegion(cellRangeAddress21);
                    row7.CreateCell(0); row7.CreateCell(1); row7.Cells[0].SetCellValue(DataTable.Rows[i]["STF_MACHINE"].ToString() + "-" + DataTable.Rows[i + 1]["STF_MACHINE"].ToString().Substring(0, 1));

                    IRow row8 = sheet1.CreateRow(7);
                    row8.CreateCell(0);
                    row8.Cells[0].SetCellValue("站位");
                    row8.Cells[0].CellStyle = style;

                    row8.CreateCell(1);
                    row8.Cells[1].SetCellValue("Feeder类型");
                    row8.Cells[1].CellStyle = style;

                    row8.CreateCell(2);
                    row8.Cells[2].SetCellValue("位置");
                    row8.Cells[2].CellStyle = style;

                    row8.CreateCell(3);
                    row8.Cells[3].SetCellValue("物料编码");
                    row8.Cells[3].CellStyle = style;

                    row8.CreateCell(4);
                    row8.Cells[4].SetCellValue("物料描述");
                    row8.Cells[4].CellStyle = style;

                    row8.CreateCell(5);
                    row8.Cells[5].SetCellValue("用量");
                    row8.Cells[5].CellStyle = style;

                    row8.CreateCell(6);
                    row8.Cells[6].SetCellValue("位号");
                    row8.Cells[6].CellStyle = style;

                    row8.CreateCell(7);
                    row8.Cells[7].SetCellValue("备注");
                    row8.Cells[7].CellStyle = style;

                    i = i + 1;
                    DataRowCount = 8;
                    sheet1.SetColumnWidth(4, sheet1.GetColumnWidth(4) + 3500);
                }
                else
                {
                    if (SheetName != "")
                    {

                        ISheet sheet = book.GetSheet(SheetName);
                        IRow row = sheet.CreateRow(DataRowCount);
                        row.CreateCell(0);
                        row.CreateCell(1);
                        row.CreateCell(2);
                        row.CreateCell(3);
                        row.CreateCell(4);
                        row.CreateCell(5);
                        row.CreateCell(6);
                        row.CreateCell(7);
                        Console.WriteLine(DataTable.Rows[i]["STF_MODEL"].ToString() + " " + DataTable.Rows[i]["STF_FEEDER"].ToString() + " " + DataTable.Rows[i]["STF_FEEDERNO"].ToString());
                        for (int k = i+1; k < DataTable.Rows.Count; k++)
                        {
                            if (k < DataTable.Rows.Count && DataTable.Rows[k]["STF_MODEL"].ToString() == "" &&
                            DataTable.Rows[k]["STF_FEEDER"].ToString() == "" &&
                            DataTable.Rows[k]["STF_FEEDERNO"].ToString() != "" && !id.Contains(k))
                            {
                                endrow++;
                                id.Add(k);
                            }
                            else
                            {
                                break;
                            }
                        }
                        //位置编号
                        if (i + endrow < DataTable.Rows.Count && DataTable.Rows[i]["STF_MODEL"].ToString() != "")
                        {
                            CellRangeAddress cellRangeAddress = new CellRangeAddress(DataRowCount, DataRowCount + endrow, 0, 0);
                            sheet.AddMergedRegion(cellRangeAddress);
                            //飞达规格
                            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(DataRowCount, DataRowCount + endrow, 1, 1);
                            sheet.AddMergedRegion(cellRangeAddress1);
                            endrow = 0;
                        }
                        row.Cells[0].SetCellValue(DataTable.Rows[i]["STF_MODEL"].ToString());
                        row.Cells[0].CellStyle = style;

                        row.Cells[1].SetCellValue(DataTable.Rows[i]["STF_FEEDER"].ToString());
                        row.Cells[1].CellStyle = style;

                        //物料编号
                        row.Cells[2].SetCellValue(DataTable.Rows[i]["STF_FEEDERNO"].ToString());
                        row.Cells[2].CellStyle = style;

                        row.Cells[3].SetCellValue(DataTable.Rows[i]["STF_LOCATION"].ToString());
                        row.Cells[3].CellStyle = style;

                        row.Cells[4].SetCellValue(DataTable.Rows[i]["pr_orispeccode"].ToString());
                        row.Cells[4].CellStyle = style;
                        row.Cells[4].CellStyle.WrapText = true;

                        row.Cells[5].SetCellValue(DataTable.Rows[i]["bd_baseqty"].ToString());
                        row.Cells[5].CellStyle = style;

                        row.Cells[6].SetCellValue(DataTable.Rows[i]["BD_LOCATION"].ToString());
                        row.Cells[6].CellStyle = style;

                        row.Cells[7].SetCellValue("");
                        row.Cells[7].CellStyle = style;

                        row.Cells[4].CellStyle.ShrinkToFit = true;
                        DataRowCount = DataRowCount + 1;
                    }

                }
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }

        /// <summary>
        /// 导出Excel,返回文件在客户端的路径
        /// </summary>
        public string ExportExcel_BAIDU(DataTable dt, DateTime begindate, int DateNum, string FolderPath)
        {
            //创建一个内存流，用来接收转换成Excel的内容
            MemoryStream ms;
            ms = DataTableToExcel_BAIDU(dt, begindate, DateNum);
            //以系统当前时间命名文件，FileMode.Create表示创建文件，FileAccess.Write表示拥有写的权限
            string filePath = @FolderPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + "各工序直通率.xls";
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            //释放当前Excel文件，否则打开文件的时候会显示文件被占用
            ms.Dispose();
            fs.Dispose();
            return filePath;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        public DataTable ImportExcel(string FolderPath, string TableName)
        {
            DataTable dt = new DataTable();
            dt.TableName = TableName;
            if (FolderPath.Contains(".xls") || FolderPath.Contains(".xlsx"))
            {
                using (FileStream file = new FileStream(FolderPath, FileMode.Open, FileAccess.Read))
                {
                    //获取文件流
                    HSSFWorkbook workbook = new HSSFWorkbook(file);
                    ISheet sheet = workbook.GetSheetAt(0);
                    //获取所有的列名
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(), typeof(string));
                    }

                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.BOOLEAN:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.ERROR:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.FORMULA:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.BOOLEAN:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.ERROR:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.NUMERIC:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.STRING:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.BLANK:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.NUMERIC:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.STRING:
                                    string strValue = item.StringCellValue;
                                    if (string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.BLANK:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        public void WriteTxt(DataTable dt, string FolderPath, string FileName)
        {
            StreamWriter sr;
            string filePath = @FolderPath;
            if (File.Exists(filePath + "\\" + FileName))   //如果文件存在,则创建File.AppendText对象   
            {
                File.Delete(filePath + "\\" + FileName);
            }
            sr = File.CreateText(filePath + "\\" + FileName);
            StringBuilder sb = new StringBuilder();
            string Title = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Title += string.Format("{0,10}", dc.ColumnName.ToString());
            }
            sr.WriteLine(Title + "\r");
            foreach (DataRow dr in dt.Rows)
            {
                string text = "";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    text += String.Format("{0,10}", dr[i].ToString());
                }
                sr.WriteLine(text + "\r");
            }
            sr.Close();
        }

        /// <summary>
        /// 将DataTable形式的数据转成Excel格式的，然后用字节流的形式写入文件
        /// </summary>
        /// <param name="DataTable"></param>
        /// <returns></returns>
        public MemoryStream DataTableToExcel(DataTable DataTable)
        {
            //创建内存流
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();
            //Excel中的Sheet
            ISheet sheet = book.CreateSheet("sheet1");
            //获取行数量和列数量
            int rowNum = DataTable.Rows.Count;
            int columnNum = DataTable.Columns.Count;
            //设置列的宽度，根据首行的列的内容的长度来设置
            for (int i = 0; i < columnNum; i++)
            {
                int dataLength = DataTable.Columns[i].ColumnName.Length;
                sheet.SetColumnWidth(i, dataLength * 700);
            }
            //首先画好第一行带颜色的，单独写出来，避免写在循环里面
            IRow row = sheet.CreateRow(0);
            //冻结第一行
            sheet.CreateFreezePane(0, 1, 0, 1);
            ICellStyle style = book.CreateCellStyle();
            style.FillForegroundColor = HSSFColor.PALE_BLUE.index;
            style.FillPattern = FillPatternType.BIG_SPOTS;
            style.FillBackgroundColor = HSSFColor.LIGHT_GREEN.index;
            //设置边框
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;
            row.HeightInPoints = 20;
            //固定第一行
            //row.RowStyle.IsLocked=true;
            //给第一行的标签赋值样式和值
            for (int j = 0; j < columnNum; j++)
            {
                row.CreateCell(j);
                row.Cells[j].CellStyle = style;
                row.Cells[j].CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                row.Cells[j].CellStyle.Alignment = HorizontalAlignment.CENTER;
                row.Cells[j].SetCellValue(DataTable.Columns[j].ColumnName);
            }

            //将DataTable的值循环赋值给book，Aligment设置居中
            //之前已经画了带颜色的第一行，所以从i=1开始画
            for (int i = 0; i < rowNum; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                row1.HeightInPoints = 20;
                for (int j = 0; j < columnNum; j++)
                {
                    row1.CreateCell(j);
                    row1.Cells[j].SetCellValue(DataTable.Rows[i][j].ToString());
                    row1.GetCell(j).CellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                }
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }

        /// <summary>
        /// 将DataTable形式的数据转成Excel格式的，然后用字节流的形式写入文件
        /// </summary>
        /// <param name="DataTable"></param>
        /// <returns></returns>
        public MemoryStream DataTableToExcel_BAIDU(DataTable DataTable, DateTime begindate, int DateNum)
        {
            string[] Step = new[] { "1-MT1", "2-MT2", "3-MMI", "4-RSA(耦合)", "5-AUD(曲线)", "6-SCW(写号)", "7-SCK(验号)" };
            string[] StepCode = new[] { "B_MT1", "B_MT2", "B_MMI", "B_RSA", "B_AUD", "B_WRITE", "B_SN", "B_OUTLOOK" };
            string[] Kind = new[] { "测试数", "通过数", "不良数", "误测通过数", "误测数", "FPY", "RPY" };
            string[] TotalKind = new[] { "总投入数", "总不良数", "FPY", "RPY" };
            string[] OutLook = new[] { "测试数", "不良数", "FPY" };
            //每行的内容
            int ContentRow = 7;
            //外观的展示的行
            int OutLookRow = 55;
            MemoryStream ms = new MemoryStream();
            //创建一个Book，相当于一个Excel文件
            HSSFWorkbook book = new HSSFWorkbook();

            ICellStyle NONE = book.CreateCellStyle();
            NONE.VerticalAlignment = VerticalAlignment.CENTER;
            NONE.Alignment = HorizontalAlignment.CENTER;
            NONE.BorderBottom = BorderStyle.THIN;
            NONE.BorderLeft = BorderStyle.THIN;
            NONE.BorderRight = BorderStyle.THIN;
            NONE.BorderTop = BorderStyle.THIN;

            ICellStyle TAN = book.CreateCellStyle();
            TAN.VerticalAlignment = VerticalAlignment.CENTER;
            TAN.Alignment = HorizontalAlignment.CENTER;
            TAN.FillForegroundColor = HSSFColor.TAN.index;
            TAN.FillPattern = FillPatternType.SOLID_FOREGROUND;
            TAN.BorderBottom = BorderStyle.THIN;
            TAN.BorderLeft = BorderStyle.THIN;
            TAN.BorderRight = BorderStyle.THIN;
            TAN.BorderTop = BorderStyle.THIN;

            ICellStyle PALE_BLUE = book.CreateCellStyle();
            PALE_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            PALE_BLUE.Alignment = HorizontalAlignment.CENTER;
            PALE_BLUE.FillForegroundColor = HSSFColor.PALE_BLUE.index;
            PALE_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            PALE_BLUE.BorderBottom = BorderStyle.THIN;
            PALE_BLUE.BorderLeft = BorderStyle.THIN;
            PALE_BLUE.BorderRight = BorderStyle.THIN;
            PALE_BLUE.BorderTop = BorderStyle.THIN;

            ICellStyle LIME = book.CreateCellStyle();
            LIME.VerticalAlignment = VerticalAlignment.CENTER;
            LIME.Alignment = HorizontalAlignment.CENTER;
            LIME.FillForegroundColor = HSSFColor.LIME.index;
            LIME.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIME.BorderBottom = BorderStyle.THIN;
            LIME.BorderLeft = BorderStyle.THIN;
            LIME.BorderRight = BorderStyle.THIN;
            LIME.BorderTop = BorderStyle.THIN;

            ICellStyle LEMON_CHIFFON = book.CreateCellStyle();
            LEMON_CHIFFON.VerticalAlignment = VerticalAlignment.CENTER;
            LEMON_CHIFFON.Alignment = HorizontalAlignment.CENTER;
            LEMON_CHIFFON.FillForegroundColor = HSSFColor.LEMON_CHIFFON.index;
            LEMON_CHIFFON.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LEMON_CHIFFON.BorderBottom = BorderStyle.THIN;
            LEMON_CHIFFON.BorderLeft = BorderStyle.THIN;
            LEMON_CHIFFON.BorderRight = BorderStyle.THIN;
            LEMON_CHIFFON.BorderTop = BorderStyle.THIN;
            LEMON_CHIFFON.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");


            ICellStyle GOLD = book.CreateCellStyle();
            GOLD.VerticalAlignment = VerticalAlignment.CENTER;
            GOLD.Alignment = HorizontalAlignment.CENTER;
            GOLD.FillForegroundColor = HSSFColor.GOLD.index;
            GOLD.FillPattern = FillPatternType.SOLID_FOREGROUND;
            GOLD.BorderBottom = BorderStyle.THIN;
            GOLD.BorderLeft = BorderStyle.THIN;
            GOLD.BorderRight = BorderStyle.THIN;
            GOLD.BorderTop = BorderStyle.THIN;

            ICellStyle LIGHT_GREEN = book.CreateCellStyle();
            LIGHT_GREEN.VerticalAlignment = VerticalAlignment.CENTER;
            LIGHT_GREEN.Alignment = HorizontalAlignment.CENTER;
            LIGHT_GREEN.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            LIGHT_GREEN.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIGHT_GREEN.BorderBottom = BorderStyle.THIN;
            LIGHT_GREEN.BorderLeft = BorderStyle.THIN;
            LIGHT_GREEN.BorderRight = BorderStyle.THIN;
            LIGHT_GREEN.BorderTop = BorderStyle.THIN;
            //LIGHT_GREEN.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            ICellStyle DARK_BLUE = book.CreateCellStyle();
            DARK_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            DARK_BLUE.Alignment = HorizontalAlignment.CENTER;
            DARK_BLUE.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
            DARK_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            DARK_BLUE.BorderBottom = BorderStyle.THIN;
            DARK_BLUE.BorderLeft = BorderStyle.THIN;
            DARK_BLUE.BorderRight = BorderStyle.THIN;
            DARK_BLUE.BorderTop = BorderStyle.THIN;
            DARK_BLUE.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            ICellStyle LIGHT_CORNFLOWER_BLUE = book.CreateCellStyle();
            LIGHT_CORNFLOWER_BLUE.VerticalAlignment = VerticalAlignment.CENTER;
            LIGHT_CORNFLOWER_BLUE.Alignment = HorizontalAlignment.CENTER;
            LIGHT_CORNFLOWER_BLUE.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            LIGHT_CORNFLOWER_BLUE.FillPattern = FillPatternType.SOLID_FOREGROUND;
            LIGHT_CORNFLOWER_BLUE.BorderBottom = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderLeft = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderRight = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.BorderTop = BorderStyle.THIN;
            LIGHT_CORNFLOWER_BLUE.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");


            ICellStyle GREY_25_PERCENT = book.CreateCellStyle();
            GREY_25_PERCENT.VerticalAlignment = VerticalAlignment.CENTER;
            GREY_25_PERCENT.Alignment = HorizontalAlignment.CENTER;
            GREY_25_PERCENT.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            GREY_25_PERCENT.FillPattern = FillPatternType.SOLID_FOREGROUND;
            GREY_25_PERCENT.BorderBottom = BorderStyle.THIN;
            GREY_25_PERCENT.BorderLeft = BorderStyle.THIN;
            GREY_25_PERCENT.BorderRight = BorderStyle.THIN;
            GREY_25_PERCENT.BorderTop = BorderStyle.THIN;

            ICellStyle PINK = book.CreateCellStyle();
            PINK.VerticalAlignment = VerticalAlignment.CENTER;
            PINK.Alignment = HorizontalAlignment.CENTER;
            PINK.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
            PINK.FillPattern = FillPatternType.SOLID_FOREGROUND;
            PINK.BorderBottom = BorderStyle.THIN;
            PINK.BorderLeft = BorderStyle.THIN;
            PINK.BorderRight = BorderStyle.THIN;
            PINK.BorderTop = BorderStyle.THIN;

            //Excel中的Sheet
            ISheet sheet = book.CreateSheet("sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            //画第一行的抬头
            cell.SetCellValue("组装制程品质数据");
            cell.CellStyle = NONE;
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, DateNum + 1);
            sheet.AddMergedRegion(region);
            //第一行的日期标题
            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.CellStyle = NONE;
            cell.SetCellValue("站点");
            cell = row.CreateCell(1);
            cell.SetCellValue("类别");
            cell.CellStyle = NONE;
            for (int i = 0; i < DateNum; i++)
            {
                cell = row.CreateCell(i + 2);
                cell.SetCellValue(begindate.AddDays(i).ToString("MM/dd"));
                cell.CellStyle = NONE;
            }
            //画第一列的工序名称和第二列的类别
            //总良率数据
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.SetCellValue("总良率");
            cell.CellStyle = LEMON_CHIFFON;
            region = new CellRangeAddress(2, 5, 0, 0);
            sheet.AddMergedRegion(region);
            //总良率的统计数据
            for (int i = 0; i < TotalKind.Length; i++)
            {
                row = sheet.GetRow(i + 2);
                if (row == null)
                {
                    row = sheet.CreateRow(i + 2);
                }
                cell = row.CreateCell(1);
                cell.SetCellValue(TotalKind[i]);
                cell.CellStyle = LEMON_CHIFFON;
                switch (i)
                {
                    case 0:
                        cell.CellStyle = LIME;
                        break;
                    case 1:
                        cell.CellStyle = TAN;
                        break;
                    case 2:
                        cell.CellStyle = DARK_BLUE;
                        break;
                    case 3:
                        cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                        break;
                    default:
                        break;
                }
            }

            //中间的设备测试工序
            for (int i = 0; i < Step.Length; i++)
            {
                //除去前面6行
                int rowindex = 6 + i * ContentRow;
                row = sheet.CreateRow(rowindex);
                cell = row.CreateCell(0);
                cell.SetCellValue(Step[i]);
                cell.CellStyle = PALE_BLUE;
                region = new CellRangeAddress(rowindex, rowindex + ContentRow - 1, 0, 0);
                sheet.AddMergedRegion(region);
                for (int j = rowindex; j < rowindex + ContentRow; j++)
                {
                    row = sheet.GetRow(j);
                    if (row == null)
                    {
                        row = sheet.CreateRow(j);
                    }
                    cell = row.CreateCell(1);
                    cell.SetCellValue(Kind[j - rowindex]);
                    switch (j - rowindex)
                    {
                        case 0:
                            cell.CellStyle = GREY_25_PERCENT;
                            break;
                        case 1:
                            cell.CellStyle = PINK;
                            break;
                        case 2:
                            cell.CellStyle = TAN;
                            break;
                        case 3:
                            cell.CellStyle = GOLD;
                            break;
                        case 4:
                            cell.CellStyle = LIGHT_GREEN;
                            break;
                        case 5:
                            cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                            break;
                        case 6:
                            cell.CellStyle = LEMON_CHIFFON;
                            break;
                        default:
                            break;
                    }
                }
            }
            //最后一行外观数据
            row = sheet.CreateRow(OutLookRow);
            cell = row.CreateCell(0);
            cell.SetCellValue("8-外观");
            cell.CellStyle = PALE_BLUE;
            region = new CellRangeAddress(OutLookRow, OutLookRow + 2, 0, 0);
            sheet.AddMergedRegion(region);

            //外观的统计数据
            for (int i = 0; i < OutLook.Length; i++)
            {
                row = sheet.GetRow(OutLookRow + i);
                if (row == null)
                {
                    row = sheet.CreateRow(OutLookRow + i);
                }
                cell = row.CreateCell(1);
                cell.SetCellValue(OutLook[i]);
                cell.CellStyle = PALE_BLUE;
                switch (i)
                {
                    case 0:
                        cell.CellStyle = GREY_25_PERCENT;
                        break;
                    case 1:
                        cell.CellStyle = TAN;
                        break;
                    case 2:
                        cell.CellStyle = LIGHT_GREEN;
                        break;
                    default:
                        break;
                }
            }
            sheet.SetColumnWidth(0, 3700);

            for (int i = 0; i < DateNum; i++)
            {
                double TotalFPY = -1;
                double TotalRPY = -1;
                double TotalNG = 0;
                double TotalIN = 0;
                for (int j = 0; j < StepCode.Length; j++)
                {
                    int rowindex = 6 + j * ContentRow;
                    DataTable dt = PublicMethod.BaseUtil.filterDataTable(DataTable, "sp_date='" + begindate.AddDays(i).ToString("yyyy-MM-dd") + "' and 工序编号='" + StepCode[j] + "'");
                    if (StepCode[j] != "B_OUTLOOK")
                    {
                        for (int k = rowindex; k < rowindex + ContentRow; k++)
                        {
                            row = sheet.GetRow(k);
                            if (row == null)
                            {
                                row = sheet.CreateRow(k);
                            }
                            cell = row.CreateCell(i + 2);
                            switch (k - rowindex)
                            {
                                case 0:
                                    double 测试数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["测试数"].ToString(), out 测试数))
                                        {
                                            cell.SetCellValue(测试数);
                                        }
                                    }
                                    cell.CellStyle = GREY_25_PERCENT;
                                    break;
                                case 1:
                                    double 通过数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["通过总数"].ToString(), out 通过数))
                                        {
                                            cell.SetCellValue(通过数);
                                        }
                                    }
                                    cell.CellStyle = PINK;
                                    break;
                                case 2:
                                    double 不良数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["不良数"].ToString(), out 不良数))
                                        {
                                            cell.SetCellValue(不良数);
                                            TotalNG = TotalNG + 不良数;
                                        }
                                    }
                                    cell.CellStyle = TAN;
                                    break;
                                case 3:
                                    double 误测通过数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["误测通过数"].ToString(), out 误测通过数))
                                        {
                                            cell.SetCellValue(误测通过数);
                                        }
                                    }
                                    cell.CellStyle = GOLD;
                                    break;
                                case 4:
                                    double 误测数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["误测数"].ToString(), out 误测数))
                                        {
                                            cell.SetCellValue(误测数);
                                        }
                                    }
                                    cell.CellStyle = LIGHT_GREEN;
                                    break;
                                case 5:
                                    double FPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["FPY"].ToString(), out FPY))
                                        {
                                            cell.SetCellValue(FPY);
                                            //累计所有FPY
                                            if (TotalFPY == -1)
                                            {
                                                TotalFPY = FPY;
                                            }
                                            else
                                            {
                                                TotalFPY = TotalFPY * FPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                                    break;
                                case 6:
                                    double RPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["RPY"].ToString(), out RPY))
                                        {
                                            cell.SetCellValue(RPY);
                                            //累计所有RPY
                                            if (TotalRPY == -1)
                                            {
                                                TotalRPY = RPY;
                                            }
                                            else
                                            {
                                                TotalRPY = TotalRPY * RPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LEMON_CHIFFON;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    else
                    {
                        for (int k = rowindex; k < rowindex + 3; k++)
                        {
                            row = sheet.GetRow(k);
                            if (row == null)
                            {
                                row = sheet.CreateRow(k);
                            }
                            cell = row.CreateCell(i + 2);
                            switch (k - rowindex)
                            {
                                case 0:
                                    double 测试数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["测试数"].ToString(), out 测试数))
                                        {
                                            cell.SetCellValue(测试数);
                                        }
                                    }
                                    cell.CellStyle = GREY_25_PERCENT;
                                    break;
                                case 1:
                                    double 不良数;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["不良数"].ToString(), out 不良数))
                                        {
                                            cell.SetCellValue(不良数);
                                            TotalNG = TotalNG + 不良数;
                                        }
                                    }
                                    cell.CellStyle = TAN;
                                    break;
                                case 2:
                                    double FPY;
                                    if (dt.Rows.Count > 0)
                                    {
                                        if (double.TryParse(dt.Rows[0]["FPY"].ToString(), out FPY))
                                        {
                                            cell.SetCellValue(FPY);
                                            //累计所有FPY
                                            if (TotalFPY == -1)
                                            {
                                                TotalFPY = FPY;
                                            }
                                            else
                                            {
                                                TotalFPY = TotalFPY * FPY;
                                            }
                                        }
                                    }
                                    cell.CellStyle = LIGHT_GREEN;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
                DataTable dt1 = PublicMethod.BaseUtil.filterDataTable(DataTable, "sp_date='" + begindate.AddDays(i).ToString("yyyy-MM-dd") + "' and 工序编号='B_LCDBA1'");
                double 投入数;
                if (dt1.Rows.Count > 0)
                {
                    if (double.TryParse(dt1.Rows[0]["测试数"].ToString(), out 投入数))
                    {
                        TotalIN = 投入数;
                    }
                }

                //设置最上方的总计数量
                row = sheet.GetRow(2);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = LIME;
                cell.SetCellValue(TotalIN);

                row = sheet.GetRow(3);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = TAN;
                cell.SetCellValue(TotalNG);

                row = sheet.GetRow(4);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = DARK_BLUE;
                cell.SetCellValue(TotalFPY == -1 ? 0 : TotalFPY);

                row = sheet.GetRow(5);
                cell = row.CreateCell(i + 2);
                cell.CellStyle = LIGHT_CORNFLOWER_BLUE;
                cell.SetCellValue(TotalRPY == -1 ? 0 : TotalRPY);
            }
            for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
            {
                sheet.GetRow(i).Height = 300;
            }
            //将book的内容写入内存流中返回
            book.Write(ms);
            return ms;
        }
    }
}