コード例 #1
0
ファイル: Program.cs プロジェクト: cqkxzyi/ZhangYi.Utilities
        void ConvertToDataTable()
        {
            XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();

            for (int j = 0; j < 5; j++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
            }

            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    XSSFCell cell = (XSSFCell)row.GetCell(i);

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            SetData(dt);
        }
コード例 #2
0
        private void GetSalaryList(ISheet mainSheet)
        {
            FileStream   salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
            XSSFWorkbook salaryWorkbook   = new XSSFWorkbook(salaryFileStream);
            XSSFSheet    salarySheet      = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");

            for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
            {
                XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
                XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
                if (sRow != null && tRow != null)
                {
                    for (int j = 0; j < 2; j++)
                    {
                        XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
                        if (sCell == null)
                        {
                            break;
                        }
                        string   cellValue = sCell.ToString();
                        XSSFCell tCell     = (XSSFCell)tRow.CreateCell(j);
                        //CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);

                        XSSFCellStyle style  = (XSSFCellStyle)sCell.CellStyle;
                        XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
                        XSSFColor     color  = null;
                        if (style.FillForegroundColorColor != null)
                        {
                            byte[] pa  = style.FillForegroundColorColor.RGB;
                            string key = pa[0] + "," + pa[1] + "," + pa[2];
                            if (dictionary.ContainsKey(key))
                            {
                                style1.FillForegroundColor = dictionary[key];
                            }
                            else
                            {
                                Console.WriteLine("找不到该颜色!" + key);
                                style1.FillForegroundColor = HSSFColor.Automatic.Index;
                            }
                        }
                        else
                        {
                            Console.WriteLine("找不到该颜色!");
                            style1.FillForegroundColor = HSSFColor.Automatic.Index;
                        }
                        //byte[] pa1 = style.FillBackgroundColorColor.RGB;
                        //style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
                        style1.FillPattern = sCell.CellStyle.FillPattern;
                        //style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
                        tCell.CellStyle = style1;
                        tCell.SetCellValue(cellValue);
                    }
                }
            }
            salaryFileStream.Close();
            salaryWorkbook.Close();
        }
コード例 #3
0
        /// <summary>
        /// 读取2007以上版本.xlsx
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string Read2007ToString(string path)
        {
            XSSFWorkbook hssfworkbook;

            path = HttpContext.Current.Server.MapPath(path);

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }

            XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();



            StringBuilder sb   = new StringBuilder();
            int           irow = 0;

            sb.Append("<table>");
            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                irow++;
                sb.Append("<tr>");
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    XSSFCell cell = (XSSFCell)row.GetCell(i);
                    string   dr   = "";
                    if (cell == null)
                    {
                        dr = "";
                    }
                    else
                    {
                        dr = cell.ToString();
                    }
                    sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
                }
                sb.Append("</tr>");
            }

            /*
             * ②:将文档保存到指定路径
             */
            string destFileName = @"D:\test.xlsx";
            //HSSFWorkbook hssfworkbook2 = writeToExcel();
            MemoryStream msfile = new MemoryStream();

            hssfworkbook.Write(msfile);
            System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
            sb.Append("</table>");
            return(sb.ToString());
        }
コード例 #4
0
ファイル: ExcelHelper.cs プロジェクト: soon14/vzan
        /// <summary>
        /// 文件为xlsx 2007
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dr"></param>
        /// <param name="i"></param>
        private static void GetCellValue(XSSFRow row, DataRow dr, int i)
        {
            XSSFCell cell = row.GetCell(i) as XSSFCell;

            if (cell != null)
            {
                switch (cell.CellType)
                {
                case CellType.Blank:
                    dr[i] = null;
                    break;

                case CellType.Boolean:
                    dr[i] = cell.BooleanCellValue;
                    break;

                case CellType.Numeric:
                    ////This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
                    if (DateTime.Compare(cell.DateCellValue, DateTime.Parse("1900-01-01")) > 0)
                    {
                        dr[i] = cell.DateCellValue;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                    //if (HSSFDateUtil.IsCellDateFormatted(cell))
                    //{
                    //    dr[i] = cell.DateCellValue;
                    //}
                    //if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                    //{
                    //    dr[i] = cell.NumericCellValue;
                    //}

                    break;

                case CellType.String:
                    dr[i] = cell.StringCellValue;
                    break;

                case CellType.Error:
                    dr[i] = cell.ErrorCellValue;
                    break;

                case CellType.Formula:
                default:
                    dr[i] = cell.NumericCellValue;
                    break;
                }
            }
        }
コード例 #5
0
    //处理excel2007
    private static void FillDataRowByHSSFRow(XSSFRow row, XSSFFormulaEvaluator evaluator, ref DataRow dr)
    {
        if (row != null)
        {
            for (int j = 0; j < dr.Table.Columns.Count; j++)
            {
                XSSFCell cell = row.GetCell(j) as XSSFCell;

                if (cell != null)
                {
                    switch (cell.CellType)
                    {
                    case CellType.BLANK:
                        dr[j] = DBNull.Value;
                        break;

                    case CellType.BOOLEAN:
                        dr[j] = cell.BooleanCellValue;
                        break;

                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            dr[j] = cell.DateCellValue;
                        }
                        else
                        {
                            dr[j] = cell.NumericCellValue;
                        }
                        break;

                    case CellType.STRING:
                        dr[j] = cell.StringCellValue;
                        break;

                    case CellType.ERROR:
                        dr[j] = cell.ErrorCellValue;
                        break;

                    case CellType.FORMULA:
                        cell  = evaluator.EvaluateInCell(cell) as XSSFCell;
                        dr[j] = cell.ToString();
                        break;

                    default:
                        throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
                    }
                }
            }
        }
    }
コード例 #6
0
ファイル: ExcelHelper.cs プロジェクト: yuzs/donetci
        /// <summary>读取excel2007
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        private static DataTable Import2007(string strFileName)
        {
            //2013/10/22 夏梁峰 add begin
            //修改使用NPOI读取excel
            //需求编号:OTS_SZDX_01_R00027_D00001
            XSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }

            DataTable dt    = new DataTable();
            ISheet    sheet = hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
            int     cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                XSSFRow row     = sheet.GetRow(i) as XSSFRow;
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return(dt);
            //2013/10/22 夏梁峰 add end

            //2013/10/22 夏梁峰 删除
            //string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            //OleDbConnection myConn = new OleDbConnection(strCon);
            //string strCom = " SELECT * FROM [Sheet1$]";
            //myConn.Open();
            //OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            //DataSet myDataSet = new DataSet();
            //myCommand.Fill(myDataSet, "[Sheet1$]");
            //myConn.Close();
            //return myDataSet.Tables[0];
        }
コード例 #7
0
        /// <summary>
        /// 读取2007以上版本.xlsx
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static DataTable Read2007ToTable(string path)
        {
            XSSFWorkbook hssfworkbook;

            path = HttpContext.Current.Server.MapPath(path);

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }

            XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();


            DataTable dt     = new DataTable();
            bool      firstr = true;

            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                #region 第一行,初始化dt
                if (firstr)
                {
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        dt.Columns.Add("column" + j);
                    }
                    firstr = false;
                }
                #endregion
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    XSSFCell cell = (XSSFCell)row.GetCell(i);
                    DataRow  dr   = dt.NewRow();
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                    dt.Rows.Add(dr);
                }
            }
            return(dt);
        }
コード例 #8
0
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            XSSFWorkbook XSSFworkbook;

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                XSSFworkbook = new XSSFWorkbook(file);
            }
            XSSFSheet sheet = (XSSFSheet)XSSFworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
            int     cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                XSSFRow row     = (XSSFRow)sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return(dt);
        }
コード例 #9
0
        // <summary>
        /// Excel某sheet中内容导入到DataTable中
        /// 区分xsl和xslx分别处理
        /// </summary>
        /// <param name="filePath">Excel文件路径,含文件全名</param>
        /// <param name="sheetName">此Excel中sheet名</param>
        /// <returns></returns>
        public static DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
        {
            DataTable dt = new DataTable();

            if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
            {//.xls
                #region .xls文件处理:HSSFWorkbook
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                ISheet sheet = hssfworkbook.GetSheet(sheetName);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    //SET EVERY COLUMN NAME
                    HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

                    dt.Columns.Add(cell.ToString());
                }

                while (rows.MoveNext())
                {
                    IRow    row = (HSSFRow)rows.Current;
                    DataRow dr  = dt.NewRow();

                    if (row.RowNum == 0)
                    {
                        continue;                 //The firt row is title,no need import
                    }
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
                        {
                            break;
                        }

                        ICell cell = row.GetCell(i);

                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }

                    dt.Rows.Add(dr);
                }
                #endregion
            }
            else
            {//.xlsx
                #region .xlsx文件处理:XSSFWorkbook
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                ISheet sheet = hssfworkbook.GetSheet(sheetName);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);



                //一行最后一个方格的编号 即总的列数
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    //SET EVERY COLUMN NAME
                    XSSFCell cell = (XSSFCell)headerRow.GetCell(j);

                    dt.Columns.Add(cell.ToString());
                }

                while (rows.MoveNext())
                {
                    IRow    row = (XSSFRow)rows.Current;
                    DataRow dr  = dt.NewRow();

                    if (row.RowNum == 0)
                    {
                        continue;                 //The firt row is title,no need import
                    }
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
                        {
                            break;
                        }

                        ICell cell = row.GetCell(i);

                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                #endregion
            }
            return(dt);
        }
コード例 #10
0
        /// <summary>读取excel
        /// 根据索引读取Sheet表数据,默认读取第一个sheet
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="sheetIndex">sheet表的索引,从0开始</param>
        /// <returns>数据集</returns>
        public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
        {
            DataTable    dt           = new DataTable();
            HSSFWorkbook hssfworkbook = null;
            XSSFWorkbook xssfworkbook = null;
            string       fileExt      = Path.GetExtension(strFileName);//获取文件的后缀名

            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xls")
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                else if (fileExt == ".xlsx")
                {
                    xssfworkbook = new XSSFWorkbook(file);//初始化太慢了,不知道这是什么bug
                }
            }
            if (hssfworkbook != null)
            {
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
                if (sheet != null)
                {
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                    int     cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row     = (HSSFRow)sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            else if (xssfworkbook != null)
            {
                XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
                if (xSheet != null)
                {
                    System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
                    XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                    int     cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                    {
                        XSSFRow row     = (XSSFRow)xSheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            return(dt);
        }
コード例 #11
0
ファイル: ExcelHelper.cs プロジェクト: zanderzhg/STO.Print
        /// <summary>读取excel
        /// 根据索引读取Sheet表数据,默认读取第一个sheet
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="sheetIndex">sheet表的索引,从0开始</param>
        /// <returns>数据集</returns>
        public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0, ProgressBar prb = null)
        {
            DataTable dt = new DataTable();

            try
            {
                HSSFWorkbook hssfworkbook = null;
                XSSFWorkbook xssfworkbook = null;
                string       fileExt      = Path.GetExtension(strFileName);//获取文件的后缀名
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (fileExt == ".xls")
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        xssfworkbook = new XSSFWorkbook(file);//初始化太慢了,不知道这是什么bug
                    }
                }
                if (hssfworkbook != null)
                {
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
                    if (sheet != null)
                    {
                        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                        int     cellCount = headerRow.LastCellNum;
                        for (int j = 0; j < cellCount; j++)
                        {
                            HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                            if (cell != null)
                            {
                                var columnName = cell.ToString();
                                foreach (var column in dt.Columns)
                                {
                                    if (column.ToString() == cell.ToString())
                                    {
                                        columnName = columnName + j;
                                        break;
                                    }
                                }
                                dt.Columns.Add(columnName, typeof(string));
                            }
                        }
                        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                        {
                            int pro = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(i / 100)));
                            if (i == pro)
                            {
                                prb.Value += 1;
                            }
                            HSSFRow row = (HSSFRow)sheet.GetRow(i);
                            if (row == null)
                            {
                                continue;
                            }
                            if (IsEmptyRow(row, null))//一整行都是空值过滤掉
                            {
                                continue;
                            }
                            DataRow dataRow = dt.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                }
                else if (xssfworkbook != null)
                {
                    XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
                    if (xSheet != null)
                    {
                        System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
                        XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                        int     cellCount = headerRow.LastCellNum;
                        for (int j = 0; j < cellCount; j++)
                        {
                            XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                            if (cell != null)
                            {
                                var columnName = cell.ToString();
                                foreach (var column in dt.Columns)
                                {
                                    if (column.ToString() == cell.ToString())
                                    {
                                        columnName = columnName + j;
                                        break;
                                    }
                                }
                                dt.Columns.Add(columnName, typeof(string));
                            }
                        }
                        for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                        {
                            XSSFRow row = (XSSFRow)xSheet.GetRow(i);
                            if (row == null)
                            {
                                continue;
                            }
                            if (IsEmptyRow(null, row))//一整行都是空值过滤掉
                            {
                                continue;
                            }
                            DataRow dataRow = dt.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return(dt);
        }
コード例 #12
0
        public static void CreateInventoryReport(string template, string savepath, List <string> datas)
        {
            XSSFWorkbook hssfworkbook;
            var          fpath = template;// HttpContext.Current.Server.MapPath(template);

            using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }

            XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            int irow = 0;

            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                irow++;
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    XSSFCell cell = (XSSFCell)row.GetCell(i);
                    string   dr   = "";
                    if (cell == null)
                    {
                        dr = "";
                    }
                    else
                    {
                        //customercode,customername,logourl,materialcode materialname,materialmodel,materialunit,formonth beginamount,begincost,inamount,incost,
                        //outamount,outcost,endamount,endcost,beginlocalamount,beginlocalcost,endlocalamount,endlocalcost
                        dr = cell.ToString();
                        string   strrow = datas.FirstOrDefault(p => p.Contains("|M001|"));
                        string[] array  = strrow.Split('|');
                        if (dr.Contains("$title$"))
                        {
                            cell.SetCellValue(dr.Replace("$title$", array[0]));
                        }
                        if (dr.Contains("$number$"))
                        {
                            cell.SetCellValue(dr.Replace("$number$", array[1]));
                        }
                        //if (dr.Contains("$customername$")) cell.SetCellValue(dr.Replace("$customername$", customername));
                        //if (dr.Contains("$starttime$")) cell.SetCellValue(dr.Replace("$starttime$", starttime));
                        //if (dr.Contains("$completetime$")) cell.SetCellValue(dr.Replace("$completetime$", completetime));
                        //if (dr.Contains("$content$")) cell.SetCellValue(dr.Replace("$content$", content));
                        //if (dr.Contains("$checkperson$")) cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
                        //if (dr.Contains("$checktime$")) cell.SetCellValue(dr.Replace("$checktime$", checktime));
                        //if (dr.Contains("$checkcontent$")) cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
                    }
                }
            }
            #region 另存为excel
            var destFileName = savepath;// HttpContext.Current.Server.MapPath(savepath);
            //HSSFWorkbook hssfworkbook2 = writeToExcel();
            MemoryStream msfile = new MemoryStream();
            hssfworkbook.Write(msfile);
            System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
            #endregion

            #region excel转化成pdf

            #endregion
        }
コード例 #13
0
        /// <summary>
        /// 读取2007以上版本.xlsx
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static void CreateServiceregulatory(string template, string savepath, string title,
                                                   string customer, string starttime, string serviceperson, string completetime, string content,
                                                   string checktime, string checkperson, string checkcontent
                                                   )
        {
            XSSFWorkbook hssfworkbook;
            var          fpath = HttpContext.Current.Server.MapPath(template);

            using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }

            XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            int irow = 0;

            while (rows.MoveNext())
            {
                XSSFRow row = (XSSFRow)rows.Current;
                irow++;
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    XSSFCell cell = (XSSFCell)row.GetCell(i);
                    string   dr   = "";
                    if (cell == null)
                    {
                        dr = "";
                    }
                    else
                    {
                        dr = cell.ToString();
                        if (dr.Contains("$title$"))
                        {
                            cell.SetCellValue(dr.Replace("$title$", title));
                        }
                        if (dr.Contains("$customer$"))
                        {
                            cell.SetCellValue(dr.Replace("$customer$", customer));
                        }
                        if (dr.Contains("$serviceperson$"))
                        {
                            cell.SetCellValue(dr.Replace("$serviceperson$", serviceperson));
                        }
                        if (dr.Contains("$starttime$"))
                        {
                            cell.SetCellValue(dr.Replace("$starttime$", starttime));
                        }
                        if (dr.Contains("$completetime$"))
                        {
                            cell.SetCellValue(dr.Replace("$completetime$", completetime));
                        }
                        if (dr.Contains("$content$"))
                        {
                            cell.SetCellValue(dr.Replace("$content$", content));
                        }
                        if (dr.Contains("$checkperson$"))
                        {
                            cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
                        }
                        if (dr.Contains("$checktime$"))
                        {
                            cell.SetCellValue(dr.Replace("$checktime$", checktime));
                        }
                        if (dr.Contains("$checkcontent$"))
                        {
                            cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
                        }
                    }
                }
            }
            #region 另存为excel
            var destFileName = HttpContext.Current.Server.MapPath(savepath);
            //HSSFWorkbook hssfworkbook2 = writeToExcel();
            MemoryStream msfile = new MemoryStream();
            hssfworkbook.Write(msfile);
            System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
            #endregion

            #region excel转化成pdf

            #endregion
        }