精华内容
下载资源
问答
  • c# 导入excel处理数据,导出excel报表//stemp1=============================//先上存一个excel文件 接收后解析获得数据#region 导入excel表并处理数据 /// /// 导入excel表并处理数据 /// api/Schedual/...

    c# 导入excel处理数据,导出excel报表

    //stemp1=============================

    //先上存一个excel文件 接收后解析获得数据

    #region 导入excel表并处理数据
    ///
    /// 导入excel表并处理数据
    /// api/Schedual/ImportExceltoData
    ///
    ///
    [HttpPost]
    [ActionName(“ImportExceltoData”)]
    public IHttpActionResult ImportExceltoData()
    {
    try
    {
    PortraitApp = “~/FileLibs/Temp/”;
    if (!Directory.Exists(HttpContext.Current.Server.MapPath(PortraitApp)))
    {
    Directory.CreateDirectory(HttpContext.Current.Server.MapPath(PortraitApp));
    }
    HttpFileCollection files = HttpContext.Current.Request.Files;
    string name = “”;
    string filename = “”;
    string path = “”;
    foreach (string key in files.AllKeys)
    {
    HttpPostedFile file = files[key];
    if (string.IsNullOrEmpty(file.FileName) == false)
    {
    int length = file.ContentLength;
    if (length > 2097152)
    {
    throw new CustomException(“上传文件超过2M,请将上传文件大小控制在2M内,谢谢”);
    }
    string extension = file.FileName.Substring(file.FileName.LastIndexOf(‘.’)).ToLower();
    if (extension != “.xls”)
    {
    throw new CustomException(“上传文件扩展名不正确,请上传xls格式的excel表”);
    }
    name = DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMddHHmmss) + extension;
    //LoginVerifyModels usermodel = GetVerifyModel();
    string username = GetVerifyString();
    if (!string.IsNullOrEmpty(username))
    {
    name = username + extension;
    }
    path = HttpContext.Current.Server.MapPath(PortraitApp) + name;
    file.SaveAs(HttpContext.Current.Server.MapPath(PortraitApp) + name);
    filename = file.FileName;
    }
    }
    DataTable dt = new ExcelHelper().ExcelToDataTable(path, filename, false);
    if (dt == null || dt.Rows.Count < 2)
    { throw new CustomException(“表格数据不能为空”); }

               //此处插入对象到数据库
                List<SchedualInfoModel> list = insertTableSchedual(dt);
    
                if (list.Count > 0)
                    return Json(Success(list));
                else
                    return Json(Success("导入失败"));
    
            }
            catch (CustomException ce)
            {
                return Json(getException(ce.Message));
            }
            catch (Exception ex)
            {
                return Json(getException(ex));
            }
        }
    

    //stemp2=============================

    //解析excel表格

    #region 通过文件获取信息
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            IWorkbook workbook = null;
            FileStream fs = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new HSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
    
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
    
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        firstRow = sheet.GetRow(2);
                        cellCount = firstRow.LastCellNum;
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    if (i == 0)
                                    {
                                        column = new DataColumn("姓名");
                                        data.Columns.Add(column);
                                    }
                                    else
                                        data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 2;
                    }
    
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null       
    
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
    
                        }
                        data.Rows.Add(dataRow);
                    }
                }
    
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
    

    //stemp0=============================

    //导出excel 工具类

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using System.Text;
    using System.Threading.Tasks;
    using System.Web;

    namespace mofa.commom
    {
    using NPOI;
    using NPOI.HPSF;
    using NPOI.HSSF;
    using NPOI.HSSF.UserModel;
    using NPOI.POIFS;
    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using NPOI.Util;
    using System.Data;

    public class ExcelHelper
    {
    
        /// <summary>
        /// 创建工作簿
        /// </summary>
        /// <param name="fileName">下载文件名</param>
        /// <param name="dt">数据源</param>
        public string CreateSheet(DataTable dt)
        {
            string DataFile = System.Configuration.ConfigurationManager.AppSettings["DataFile"].ToString();
            string filepath = HttpContext.Current.Server.MapPath(DataFile) + DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "\\";
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            FolderDeal(HttpContext.Current.Server.MapPath(DataFile));
    
            StringBuilder builder = new StringBuilder();
    
            string name = System.DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMddHHmmss) + ".xls";
            string fileName = filepath + name;
    
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook(); ;
            //string extension = System.IO.Path.GetExtension(fileName);
    
    
            //HSSFWorkbook workbook = new HSSFWorkbook();
            //Stream ms = new MemoryStream();
    
            //创建一个名称为Payment的工作表
            ISheet paymentSheet = workbook.CreateSheet("Payment");
    
            //数据源
            DataTable tbPayment = dt;
    
            //头部标题
            IRow paymentHeaderRow = paymentSheet.CreateRow(0);
    
            //循环添加标题
    
            foreach (DataColumn column in tbPayment.Columns)
            {
                paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                //paymentHeaderRow.Height = (short)3000;
            }
    
            ICellStyle style = workbook.CreateCellStyle();//样式
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREEN.index;
    
            // 内容
            int paymentRowIndex = 1;
    
            foreach (DataRow row in tbPayment.Rows)
            {
                IRow newRow = paymentSheet.CreateRow(paymentRowIndex);
    
                //循环添加列的对应内容
                foreach (DataColumn column in tbPayment.Columns)
                {
                    newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                newRow.RowStyle = style;
                paymentRowIndex++;
            }
    
            //列宽自适应,只对英文和数字有效
            for (int i = 0; i <= dt.Rows.Count; i++)
            {
                paymentSheet.AutoSizeColumn(i);
            }
            //获取当前列的宽度,然后对比本列的长度,取最大值
            for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
            {
                int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (paymentSheet.GetRow(rowNum) == null)
                    {
                        currentRow = paymentSheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = paymentSheet.GetRow(rowNum);
                    }
    
                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
            }
    
            //将表内容写入流 通知浏览器下载
            //workbook.Write(ms);
            //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
            //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); //进行二进制流下在
            try
            {
                FileStream fs = File.OpenWrite(fileName);
                workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
                fs.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            workbook = null;
            //ms.Close();
            //ms.Dispose();
    
            return DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "/" + name;
        }
    
        private void FolderDeal(string folderFullName)
        {
            //throw new NotImplementedException();
            DirectoryInfo TheFolder = new DirectoryInfo(folderFullName);
            if (TheFolder.GetDirectories().Count() > 1)
            {
                foreach (DirectoryInfo item in TheFolder.GetDirectories())
                {
                    DateTime now = DateTime.Now;
                    if (item.Name != now.ToStringByDatetime(DateTimeType.yyyyMMdd) && item.Name != now.AddDays(-1).ToStringByDatetime(DateTimeType.yyyyMMdd))
                    {
                        item.Delete(true);
                    }
                }
            }
        }
    
        public string CreateSheet(DataTable dt, string fName, bool isSpan = false)
        {
            string DataFile = System.Configuration.ConfigurationManager.AppSettings["DataFile"].ToString();
            string filepath = HttpContext.Current.Server.MapPath(DataFile) + DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "\\";
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            FolderDeal(HttpContext.Current.Server.MapPath(DataFile));
    
            StringBuilder builder = new StringBuilder();
    
            string name = fName + System.DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMddHHmmss) + ".xls";
            string fileName = filepath + name;
    
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook(); ;
            //string extension = System.IO.Path.GetExtension(fileName);
    
    
            //HSSFWorkbook workbook = new HSSFWorkbook();
            //Stream ms = new MemoryStream();
    
            //创建一个名称为Payment的工作表
            ISheet paymentSheet = workbook.CreateSheet("Payment");
    
            //数据源
            DataTable tbPayment = dt;
    
            //头部标题
            IRow paymentHeaderRow = paymentSheet.CreateRow(0);
    
            //循环添加标题
    
            foreach (DataColumn column in tbPayment.Columns)
            {
                paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                //paymentHeaderRow.Height = (short)3000;
            }
    
            ICellStyle style = workbook.CreateCellStyle();//样式
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREEN.index;
    
            // 内容
            int paymentRowIndex = 1;
    
            foreach (DataRow row in tbPayment.Rows)
            {
                IRow newRow = paymentSheet.CreateRow(paymentRowIndex);
    
                //循环添加列的对应内容
                foreach (DataColumn column in tbPayment.Columns)
                {
                    newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                newRow.RowStyle = style;
                paymentRowIndex++;
            }
    
            //列宽自适应,只对英文和数字有效
            for (int i = 0; i <= dt.Rows.Count; i++)
            {
                paymentSheet.AutoSizeColumn(i);
            }
            //获取当前列的宽度,然后对比本列的长度,取最大值
            for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
            {
                int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (paymentSheet.GetRow(rowNum) == null)
                    {
                        currentRow = paymentSheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = paymentSheet.GetRow(rowNum);
                    }
    
                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
            }
    
            //将表内容写入流 通知浏览器下载
            //workbook.Write(ms);
            //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
            //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); //进行二进制流下在
            try
            {
                FileStream fs = File.OpenWrite(fileName);
                workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
                fs.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            workbook = null;
            //ms.Close();
            //ms.Dispose();
    
            return DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "/" + name;
        }
    
        public string CreateDetailSheet(DataTable dt, string fName)
        {
    
            string DataFile = System.Configuration.ConfigurationManager.AppSettings["DataFile"].ToString();
            string filepath = HttpContext.Current.Server.MapPath(DataFile) + DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "\\";
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            FolderDeal(HttpContext.Current.Server.MapPath(DataFile));
    
            StringBuilder builder = new StringBuilder();
    
            string name = fName + System.DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMddHHmmss) + ".xls";
            string fileName = filepath + name;
    
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook(); ;
    
            //创建一个名称为Payment的工作表
            ISheet paymentSheet = workbook.CreateSheet("Payment");
    
            //数据源
            DataTable tbPayment = dt;
    
            //头部标题
            IRow paymentHeaderRow = paymentSheet.CreateRow(0);
    
            //循环添加标题
    
            foreach (DataColumn column in tbPayment.Columns)
            {
                paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                //paymentHeaderRow.Height = (short)3000;
            }
    
            ICellStyle style = workbook.CreateCellStyle();//样式
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREEN.index;
    
            // 内容
            int paymentRowIndex = 1;
    
            //foreach (DataRow row in tbPayment.Rows)
            //{
            //    IRow newRow = paymentSheet.CreateRow(paymentRowIndex);
    
            //    //循环添加列的对应内容
            //    foreach (DataColumn column in tbPayment.Columns)
            //    {
            //        newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            //    }
            //    newRow.RowStyle = style;
            //    paymentRowIndex++;
            //}
            string tid = tbPayment.Rows.Count > 0 ? tbPayment.Rows[0]["订单编号"].ToString() : "";
            string oid = tbPayment.Rows.Count > 0 ? tbPayment.Rows[0]["订单ID"].ToString() : "";
            int tspan = 0;
            int ospan = 0;
            for (int i = 0; i < tbPayment.Rows.Count; i++)
            {
                IRow newRow = paymentSheet.CreateRow(paymentRowIndex);
    
                //循环添加列的对应内容
                foreach (DataColumn column in tbPayment.Columns)
                {
                    newRow.CreateCell(column.Ordinal).SetCellValue(tbPayment.Rows[i][column].ToString());
                }
    
                if (i > 0)
                {
                    if (tid == tbPayment.Rows[i]["订单编号"].ToString())
                    {
                        tspan++;
                        for (int j = 0; j < 6; j++)
                        {
                            //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
                            paymentSheet.AddMergedRegion(new CellRangeAddress(i - tspan + 1, i + 1, j, j));
                        }
                    }
                    else
                    {
                        tid = tbPayment.Rows[i]["订单编号"].ToString();
                        tspan = 0;
                    }
    
                    if (oid == tbPayment.Rows[i]["订单ID"].ToString())
                    {
                        ospan++;
                        for (int k = 6; k < 19; k++)
                        {
                            paymentSheet.AddMergedRegion(new CellRangeAddress(i - ospan + 1, i + 1, k, k));
                        }
                    }
                    else
                    {
                        oid = tbPayment.Rows[i]["订单ID"].ToString();
                        ospan = 0;
                    }
    
                }
                newRow.RowStyle = style;
                paymentRowIndex++;
            }
    
            //列宽自适应,只对英文和数字有效
            for (int i = 0; i <= dt.Rows.Count; i++)
            {
                paymentSheet.AutoSizeColumn(i);
            }
            //获取当前列的宽度,然后对比本列的长度,取最大值
            for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
            {
                int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (paymentSheet.GetRow(rowNum) == null)
                    {
                        currentRow = paymentSheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = paymentSheet.GetRow(rowNum);
                    }
    
                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
            }
    
            try
            {
                FileStream fs = File.OpenWrite(fileName);
                workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
                fs.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            workbook = null;
    
            return DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "/" + name;
        }
    
    
        #region 导出排班Excel报表
        public string DeriveExcel(System.Data.DataTable table, string shopname, string yearmonth)
        {
            string DataFile = System.Configuration.ConfigurationManager.AppSettings["DataFile"].ToString();
            string filepath = HttpContext.Current.Server.MapPath(DataFile) + DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "\\";
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            FolderDeal(HttpContext.Current.Server.MapPath(DataFile));
    
            FileInfo f = null;
            try
            {
                //获取年,月
                int year = Convert.ToInt32(yearmonth.Substring(0, 4));
                int month = Convert.ToInt32(yearmonth.Substring(4));
                //获取当前月天数
                int days = DateTime.DaysInMonth(year, month);
                //行号,叠加
                int rowNum = 0;
    
                HSSFWorkbook workbook = new HSSFWorkbook();
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, days + 2);
    
                //创建表
                var sheet = workbook.CreateSheet(string.Format("{0}{1}月份排班表", shopname, month));
                //首行样式
                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font = workbook.CreateFont();
                font.FontHeightInPoints = (short)20;
                style.SetFont(font);
                IRow row = sheet.CreateRow(rowNum++);
                ICell cell = row.CreateCell(0);
                sheet.AddMergedRegion(cellRangeAddress);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0}{1}月份排班表", shopname, month));
    
                //添加班次类型行
                ICellStyle timeTypeStyle = workbook.CreateCellStyle();
                timeTypeStyle.Alignment = HorizontalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
                font2.FontHeightInPoints = (short)12;
                timeTypeStyle.SetFont(font2);
                CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 0, days + 2);
                sheet.AddMergedRegion(cellRangeAddress2);
                row = sheet.CreateRow(rowNum++);
                cell = row.CreateCell(0);
                cell.CellStyle = timeTypeStyle;
                cell.SetCellValue("{早班(A)09:30-17:30  中班(B)12:00-20:00  晚班(C)15:30-22:00}离职·自离·请假·休息}");
                //日期行
                ICellStyle style2 = workbook.CreateCellStyle();
                style2.Alignment = HorizontalAlignment.CENTER;
                style2.VerticalAlignment = VerticalAlignment.CENTER;
                row = sheet.CreateRow(rowNum++);
                row.Height = 400;
                cell = row.CreateCell(0);
                cell.CellStyle = style2;
                cell.SetCellValue("用户名");
                for (int j = 1; j <= days; j++)
                {
                    cell = row.CreateCell(j);
                    cell.CellStyle = style2;
                    cell.SetCellValue(j);
                }
                cell = row.CreateCell(days + 1);
                cell.CellStyle = style2;
                cell.SetCellValue("合计");
                cell = row.CreateCell(days + 2);
                cell.CellStyle = style2;
                cell.SetCellValue("备注");
    
    
                //星期行
                //红色居中样式
                ICellStyle style3 = workbook.CreateCellStyle();
                style3.Alignment = HorizontalAlignment.CENTER;
                style3.VerticalAlignment = VerticalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font3 = workbook.CreateFont();
                font3.Color = (short)FontColor.RED;
                style3.SetFont(font3);
                row = sheet.CreateRow(rowNum++);
                row.Height = 400;
                cell = row.CreateCell(0);
                cell.CellStyle = style2;
                cell.SetCellValue("星期");
                string[] Day = new string[] { "日", "一", "二", "三", "四", "五", "六" };
                int workDay = 0;
                for (int i = 1; i <= days; i++)
                {
                    DateTime time = Convert.ToDateTime(year.ToString() + "-" + month.ToString() + "-" + i.ToString());
                    string week = Day[Convert.ToInt32(time.DayOfWeek.ToString("d"))].ToString();
                    cell = row.CreateCell(i);
                    if (week == "六" || week == "日")
                    {
                        cell.CellStyle = style3;
                    }
                    else
                    {
                        cell.CellStyle = style2;
                    }
                    //合计工作日
                    if (week != "六" && week != "日")
                    {
                        workDay++;
                    }
                    cell.SetCellValue(week);
                }
                cell = row.CreateCell(days + 1);
                cell.CellStyle = style2;
                cell.SetCellValue("班:" + workDay + " 休:" + (days - workDay));
                cell = row.CreateCell(days + 2);
                cell.SetCellValue("");
                //添加数据库信息
                foreach (DataRow item in table.Rows)
                {
                    //名字
                    row = sheet.CreateRow(rowNum++);
                    row.Height = 400;
                    cell = row.CreateCell(0);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["Name"].ToString());
                    //排班信息
                    for (int i = 1; i <= days; i++)
                    {
                        cell = row.CreateCell(i);
                        cell.CellStyle = style2;
                        cell.SetCellValue(item["day" + i.ToString()].ToString());
                    }
                    //合计,备注
                    cell = row.CreateCell(days + 1);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["ActualDay"].ToString() == "Empty" ? "" : item["ActualDay"].ToString());
                    cell = row.CreateCell(days + 2);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["remark"].ToString() == "Empty" ? "" : item["remark"].ToString());
                }
    
                CellRangeAddress cellRangeAddress3 = new CellRangeAddress(rowNum, rowNum, 0, days + 2);
                sheet.AddMergedRegion(cellRangeAddress3);
                row = sheet.CreateRow(rowNum++);
                cell = row.CreateCell(0);
                cell.CellStyle = style3;
                cell.SetCellValue("备注:周六日休息及连休超过三天请征得店长同意,排班后每月只有四次更改机会,更改班表的请备注。");
    
    
                int fileNum = 1;
                string filename = string.Format("{0}{1}年{2}月份排班信息.xls", shopname, year, month);
                f = new FileInfo(filepath + filename);
    
                while (f.Exists)
                {
                    filename = string.Format("{0}{1}年{2}月份排班信息({3}).xls", shopname, year, month, fileNum++);
                    f = new FileInfo(filepath + filename);
                }
    
                using (var fs = f.OpenWrite())
                {
                    workbook.Write(fs);   //向打开的这个xls文件中写入Sheet表并保存。
                }
                return filename;
            }
            catch (Exception)
            {
                throw;
            }
    
        }
        #endregion
    
        #region 导出排班Excel报表模板
        public string DeriveExcelModel(System.Data.DataTable table, string shopname, string yearmonth)
        {
            string DataFile = System.Configuration.ConfigurationManager.AppSettings["DataFile"].ToString();
            string filepath = HttpContext.Current.Server.MapPath(DataFile) + DateTime.Now.ToStringByDatetime(DateTimeType.yyyyMMdd) + "\\";
            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            FolderDeal(HttpContext.Current.Server.MapPath(DataFile));
    
            FileInfo f = null;
            try
            {
                //获取年,月
                int year = Convert.ToInt32(yearmonth.Substring(0, 4));
                int month = Convert.ToInt32(yearmonth.Substring(4));
                //获取当前月天数
                int days = DateTime.DaysInMonth(year, month);
                //行号,叠加
                int rowNum = 0;
    
                HSSFWorkbook workbook = new HSSFWorkbook();
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, days + 6);
    
                //创建表
                var sheet = workbook.CreateSheet(string.Format("{0}{1}月份排班表", shopname, month));
                //首行样式
                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font = workbook.CreateFont();
                font.FontHeightInPoints = (short)20;
                style.SetFont(font);
                IRow row = sheet.CreateRow(rowNum++);
                ICell cell = row.CreateCell(0);
                sheet.AddMergedRegion(cellRangeAddress);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0}{1}月份排班表", shopname, month));
    
                //添加班次类型行
                ICellStyle timeTypeStyle = workbook.CreateCellStyle();
                timeTypeStyle.Alignment = HorizontalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
                font2.FontHeightInPoints = (short)12;
                timeTypeStyle.SetFont(font2);
                CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 0, days + 6);
                sheet.AddMergedRegion(cellRangeAddress2);
                row = sheet.CreateRow(rowNum++);
                cell = row.CreateCell(0);
                cell.CellStyle = timeTypeStyle;
                cell.SetCellValue("{早班(A)09:30-17:30  中班(B)12:00-20:00  晚班(C)15:30-22:00}离职·自离·请假·休息}");
                //日期行
                ICellStyle style2 = workbook.CreateCellStyle();
                style2.Alignment = HorizontalAlignment.CENTER;
                style2.VerticalAlignment = VerticalAlignment.CENTER;
                row = sheet.CreateRow(rowNum++);
                row.Height = 400;
                cell = row.CreateCell(0);
                cell.CellStyle = style2;
                cell.SetCellValue(shopname);
                cell = row.CreateCell(1);
                cell.CellStyle = style2;
                cell.SetCellValue(yearmonth);
                for (int j = 1; j <= days; j++)
                {
                    cell = row.CreateCell(j + 1);
                    cell.CellStyle = style2;
                    cell.SetCellValue(j + "号");
                }
                cell = row.CreateCell(days + 2);
                cell.CellStyle = style2;
                cell.SetCellValue("迟到/早退/分");
                cell = row.CreateCell(days + 3);
                cell.CellStyle = style2;
                cell.SetCellValue("实际出勤/天");
                cell = row.CreateCell(days + 4);
                cell.CellStyle = style2;
                cell.SetCellValue("法定节日加班/天");
                cell = row.CreateCell(days + 5);
                cell.CellStyle = style2;
                cell.SetCellValue("车费补贴");
                cell = row.CreateCell(days + 6);
                cell.CellStyle = style2;
                cell.SetCellValue("备注");
    
    
                //星期行
                //红色居中样式
                ICellStyle style3 = workbook.CreateCellStyle();
                style3.Alignment = HorizontalAlignment.CENTER;
                style3.VerticalAlignment = VerticalAlignment.CENTER;
                NPOI.SS.UserModel.IFont font3 = workbook.CreateFont();
                font3.Color = (short)FontColor.RED;
                style3.SetFont(font3);
                row = sheet.CreateRow(rowNum++);
                row.Height = 400;
                cell = row.CreateCell(0);
                cell.CellStyle = style2;
                cell.SetCellValue("姓名");
                cell = row.CreateCell(1);
                cell.CellStyle = style2;
                cell.SetCellValue("入职日期\\星期");
                string[] Day = new string[] { "日", "一", "二", "三", "四", "五", "六" };
                int workDay = 0;
                for (int i = 1; i <= days; i++)
                {
                    DateTime time = Convert.ToDateTime(year.ToString() + "-" + month.ToString() + "-" + i.ToString());
                    string week = Day[Convert.ToInt32(time.DayOfWeek.ToString("d"))].ToString();
                    cell = row.CreateCell(i + 1);
                    if (week == "六" || week == "日")
                    {
                        cell.CellStyle = style3;
                    }
                    else
                    {
                        cell.CellStyle = style2;
                    }
                    //合计工作日
                    if (week != "六" && week != "日")
                    {
                        workDay++;
                    }
                    cell.SetCellValue(week);
                }
                cell = row.CreateCell(days + 2);
                cell.CellStyle = style2;
                cell.SetCellValue("班:" + workDay + " 休:" + (days - workDay));
                cell = row.CreateCell(days + 3);
                cell.SetCellValue("");
                //添加数据库信息
                foreach (DataRow item in table.Rows)
                {
                    //名字
                    row = sheet.CreateRow(rowNum++);
                    row.Height = 400;
                    cell = row.CreateCell(0);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["Name"].ToString());
                    //排班信息
                    for (int i = 1; i <= days; i++)
                    {
                        cell = row.CreateCell(i + 1);
                        cell.CellStyle = style2;
                        cell.SetCellValue(item["day" + i.ToString()].ToString() == "" ? "" : item["day" + i.ToString()].ToString());
                    }
                    //迟到/早退/分
                    cell = row.CreateCell(days + 2);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["lateOrEarly"].ToString() == "Empty" ? "" : item["lateOrEarly"].ToString());
                    //实际出勤/天
                    cell = row.CreateCell(days + 3);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["ActualDay"].ToString() == "Empty" ? "" : item["ActualDay"].ToString());
                    //法定节日加班/天
                    cell = row.CreateCell(days + 4);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["outDay"].ToString() == "Empty" ? "" : item["outDay"].ToString());
                    //车费补贴
                    cell = row.CreateCell(days + 5);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["CarSubsidy"].ToString() == "Empty" ? "" : item["CarSubsidy"].ToString());
                    //备注
                    cell = row.CreateCell(days + 6);
                    cell.CellStyle = style2;
                    cell.SetCellValue(item["remark"].ToString() == "Empty" ? "" : item["remark"].ToString());
                }
    
                CellRangeAddress cellRangeAddress3 = new CellRangeAddress(rowNum, rowNum, 0, days + 2);
                sheet.AddMergedRegion(cellRangeAddress3);
                row = sheet.CreateRow(rowNum++);
                cell = row.CreateCell(0);
                cell.CellStyle = style3;
                cell.SetCellValue("备注:除修改排班信息外,请勿修改表格格式,以免导入排班异常.周六日休息及连休超过三天请征得店长同意,排班后每月只有四次更改机会,更改班表的请备注。");
    
    
                int fileNum = 1;
                string filename = string.Format("{0}{1}年{2}月份排班信息.xls", shopname, year, month);
                f = new FileInfo(filepath + filename);
    
                while (f.Exists)
                {
                    filename = string.Format("{0}{1}年{2}月份排班信息({3}).xls", shopname, year, month, fileNum++);
                    f = new FileInfo(filepath + filename);
                }
    
                using (var fs = f.OpenWrite())
                {
                    workbook.Write(fs);   //向打开的这个xls文件中写入Sheet表并保存。
                }
                return filename;
            }
            catch (Exception)
            {
                throw;
            }
    
        }
        #endregion
    
        #region 通过文件获取信息
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            IWorkbook workbook = null;
            FileStream fs = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new HSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
    
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
    
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        firstRow = sheet.GetRow(2);
                        cellCount = firstRow.LastCellNum;
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    if (i == 0)
                                    {
                                        column = new DataColumn("姓名");
                                        data.Columns.Add(column);
                                    }
                                    else
                                        data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 2;
                    }
    
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null       
    
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
    
                        }
                        data.Rows.Add(dataRow);
                    }
                }
    
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
        #endregion
    
    }
    

    }

    展开全文
  • 大家好,我是小z今天给大家来一波实战,使用Python自动生成数据报表!从一条条的数据中,创建出一张数据报表,得出你想要的东西,提高效率。主要使用到pandas、xlwings以及mat...

    大家好,我是小z

    今天给大家来一波实战,使用Python自动化生成数据报表!

    从一条条的数据中,创建出一张数据报表,得出你想要的东西,提高效率。

    主要使用到pandas、xlwings以及matplotlib这几个库。

    先来看一下动态的GIF,都是程序自动生成。

    下面我们就来看看这个案例吧,水果蔬菜销售报表。

    原始数据如下,主要有水果蔬菜名称、销售日期、销售数量、平均价格、平均成本、总收入、总成本、总利润等。

    先导入相关库,使用pandas读取原始数据。

    import pandas as pd
    import xlwings as xw
    import matplotlib.pyplot as plt
    
    # 对齐数据
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)
    
    # 读取数据
    df = pd.read_csv(r"fruit_and_veg_sales.csv")
    print(df)
    

    结果如下。

    一共是有1000行的销售数据。

    使用xlwings库创建一个Excel工作簿,在工作簿中创建一个表,表名为fruit_and_veg_sales,然后将原始数据复制进去。

    # 创建原始数据表并复制数据
    wb = xw.Book()
    sht = wb.sheets["Sheet1"]
    sht.name = "fruit_and_veg_sales"
    sht.range("A1").options(index=False).value = d
    
    

    关于xlwings库的使用,推荐两个文档地址

    中文版:

    https://www.kancloud.cn/gnefnuy/xlwings-docs/1127455

    英文版:

    https://docs.xlwings.org/en/stable/index.html

    推荐使用中文版,可以降低学习难度...

    当然关于Excel的VBA操作,也可以看看微软的文档。

    地址:

    https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel

    将原始数据取过来后,再在工作簿中创建一个可视化表,即Dashboard表。

    # 创建表
    wb.sheets.add('Dashboard')
    sht_dashboard = wb.sheets('Dashboard')
    
    

    现在,我们有了一个包含两个工作表的Excel工作簿。fruit_and_veg_sales表有我们的数据,Dashboard表则是空白的。

    下面使用pandas来处理数据,生成Dashboard表的数据信息。

    DashBoard表的头两个表格,一个是产品的利润表格,一个是产品的销售数量表格。

    使用到了pandas的数据透视表函数。

    # 总利润透视表
    pv_total_profit = pd.pivot_table(df, index='类别', values='总利润(美元)', aggfunc='sum')
    print(pv_total_profit)
    
    # 销售数量透视表
    pv_quantity_sold = pd.pivot_table(df, index='类别', values='销售数量', aggfunc='sum')
    print(pv_quantity_sold)
    
    

    得到数据如下。

    稍后会将数据放置到Excel的表中去。

    下面对月份进行分组汇总,得出每个月的销售情况。

    # 查看每列的数据类型
    print(df.dtypes)
    df["销售日期"] = pd.to_datetime(df["销售日期"])
    
    # 每日的数据情况
    gb_date_sold = df.groupby(df["销售日期"].dt.to_period('m')).sum()[["销售数量", '总收入(美元)', '总成本(美元)', "总利润(美元)"]]
    gb_date_sold.index = gb_date_sold.index.to_series().astype(str)
    print(gb_date_sold)
    
    

    得到结果如下。

    这里先对数据进行了查询,发现日期列为object,是不能进行分组汇总的。

    所以使用了pd.to_datetime()对其进行了格式转换,而后根据时间进行分组汇总,得到每个月的数据情况。

    最后一个groupby将为Dashboard表提供第四个数据信息。

    # 总收入前8的日期数据
    gb_top_revenue = (df.groupby(df["销售日期"])
        .sum()
        .sort_values('总收入(美元)', ascending=False)
        .head(8)
        )[["销售数量", '总收入(美元)', '总成本(美元)', "总利润(美元)"]]
    print(gb_top_revenue)
    
    

    总收入前8的日期,得到结果如下。

    现在我们有了4份数据,可以将其附加到Excel中。

    # 设置背景颜色, 从A1单元格到Z1000单元格的矩形区域
    sht_dashboard.range('A1:Z1000').color = (198, 224, 180)
    
    # A、B列的列宽
    sht_dashboard.range('A:B').column_width = 2.22
    print(sht_dashboard.range('B2').api.font_object.properties.get())
    # B2单元格, 文字内容、字体、字号、粗体、颜色、行高(主标题)
    sht_dashboard.range('B2').value = '销售数据报表'
    sht_dashboard.range('B2').api.font_object.name.set('黑体')
    sht_dashboard.range('B2').api.font_object.font_size.set(48)
    sht_dashboard.range('B2').api.font_object.bold.set(True)
    sht_dashboard.range('B2').api.font_object.color.set([0, 0, 0])
    sht_dashboard.range('B2').row_height = 61.2
    
    # B2单元格到W2单元格的矩形区域, 下边框的粗细及颜色
    sht_dashboard.range('B2:W2').api.get_border(which_border=9).weight.set(4)
    sht_dashboard.range('B2:W2').api.get_border(which_border=9).color.set([0, 176, 80])
    
    # 不同产品总的收益情况图表名称、字体、字号、粗体、颜色(副标题)
    sht_dashboard.range('M2').value = '每种产品的收益情况'
    sht_dashboard.range('M2').api.font_object.name.set('黑体')
    sht_dashboard.range('M2').api.font_object.font_size.set(20)
    sht_dashboard.range('M2').api.font_object.bold.set(True)
    sht_dashboard.range('M2').api.font_object.color.set([0, 0, 0])
    
    # 主标题和副标题的分割线, 粗细、颜色、线型
    sht_dashboard.range('L2').api.get_border(which_border=7).weight.set(3)
    sht_dashboard.range('L2').api.get_border(which_border=7).color.set([0, 176, 80])
    sht_dashboard.range('L2').api.get_border(which_border=7).line_style.set(-4115)
    

    先配置一些基本内容,比如文字,颜色背景,边框线等,如下图。

    使用函数,批量生成四个表格的格式。

    # 表格生成函数.
    def create_formatted_summary(header_cell, title, df_summary, color):
        """
        Parameters
        ----------
        header_cell : Str
            左上角单元格位置, 放置数据
    
        title : Str
            当前表格的标题
    
        df_summary : DataFrame
            表格的数据
    
        color : Str
            表格填充色
        """
    
        # 可选择的表格填充色
        colors = {"purple": [(112, 48, 160), (161, 98, 208)],
                  "blue": [(0, 112, 192), (155, 194, 230)],
                  "green": [(0, 176, 80), (169, 208, 142)],
                  "yellow": [(255, 192, 0), (255, 217, 102)]}
    
        # 设置表格标题的列宽
        sht_dashboard.range(header_cell).column_width = 1.5
    
        # 获取单元格的行列数
        row, col = sht_dashboard.range(header_cell).row, sht_dashboard.range(header_cell).column
    
        # 设置表格的标题及相关信息, 如:字号、行高、向左居中对齐、颜色、粗体、表格的背景颜色等
        summary_title_range = sht_dashboard.range((row, col))
        summary_title_range.value = title
        summary_title_range.api.font_object.font_size.set(14)
        summary_title_range.row_height = 32.5
        # 垂直对齐方式
        summary_title_range.api.verticalalignment = xw.constants.HAlign.xlHAlignCenter
        summary_title_range.api.font_object.color.set([255, 255, 255])
        summary_title_range.api.font_object.bold.set(True)
        sht_dashboard.range((row, col),
                            (row, col + len(df_summary.columns) + 1)).color = colors[color][0]  # Darker color
    
        # 设置表格内容、起始单元格、数据填充、字体大小、粗体、颜色填充
        summary_header_range = sht_dashboard.range((row + 1, col + 1))
        summary_header_range.value = df_summary
        summary_header_range = summary_header_range.expand('right')
        summary_header_range.api.font_object.font_size.set(11)
        summary_header_range.api.font_object.bold.set(True)
        sht_dashboard.range((row + 1, col),
                            (row + 1, col + len(df_summary.columns) + 1)).color = colors[color][1]  # Darker color
        sht_dashboard.range((row + 1, col + 1),
                            (row + len(df_summary), col + len(df_summary.columns) + 1)).autofit()
    
        for num in range(1, len(df_summary) + 2, 2):
            sht_dashboard.range((row + num, col),
                                (row + num, col + len(df_summary.columns) + 1)).color = colors[color][1]
    
        # 找到表格的最后一行
        last_row = sht_dashboard.range((row + 1, col + 1)).expand('down').last_cell.row
        side_border_range = sht_dashboard.range((row + 1, col), (last_row, col))
    
        # 给表格左边添加带颜色的边框
        side_border_range.api.get_border(which_border=7).weight.set(3)
        side_border_range.api.get_border(which_border=7).color.set(colors[color][1])
        side_border_range.api.get_border(which_border=7).line_style.set(-4115)
    
    
    # 生成4个表格
    create_formatted_summary('B5', '每种产品的收益情况', pv_total_profit, 'green')
    create_formatted_summary('B17', '每种产品的售出情况', pv_quantity_sold, 'purple')
    create_formatted_summary('F17', '每月的销售情况', gb_date_sold, 'blue')
    create_formatted_summary('F5', '每日总收入排名Top8 ', gb_top_revenue, 'yellow')
    
    

    得到结果如下。

    可以看到,一行行的数据经过Python的处理,变为一目了然的表格。

    最后再绘制一个matplotlib图表,添加一张logo图片,并保存Excel文件。

    # 中文显示
    plt.rcParams['font.sans-serif']=['Songti SC']
    
    # 使用Matplotlib绘制可视化图表, 饼图
    fig, ax = plt.subplots(figsize=(6, 3))
    pv_total_profit.plot(color='g', kind='bar', ax=ax)
    
    # 添加图表到Excel
    sht_dashboard.pictures.add(fig, name='ItemsChart',
                               left=sht_dashboard.range("M5").left,
                               top=sht_dashboard.range("M5").top,
                               update=True)
    
    # 添加logo到Excel
    logo = sht_dashboard.pictures.add(image="pie_logo.png",
                               name='PC_3',
                               left=sht_dashboard.range("J2").left,
                               top=sht_dashboard.range("J2").top+5,
                               update=True)
    
    # 设置logo的大小
    logo.width = 54
    logo.height = 54
    
    # 保存Excel文件
    wb.save(rf"水果蔬菜销售报表.xlsx")
    
    

    此处需设置一下中文显示,否则会显示不了中文,只有一个个方框。

    得到最终的水果蔬菜销售报表。

    本文的示例代码,可以在Mac+Excel2016中运行的,与Windows还是会有一些区别,API函数的调用(pywin32 or appscript)。

    比如表格文字的字体设置。

    # Windows
    sht_dashboard.range('B2').api.font.name = '黑体'
    
    # Mac
    sht_dashboard.range('B2').api.font_object.name.set('黑体')
    

    文中代码和数据源已经打包好了,具体的地址:

    链接:https://pan.baidu.com/s/11aUOqbnoY6PDpGXrzbzxDw

    提取码:niub

    详解用户流失原因分析该如何入手?

    12000+字超详细 SQL 语法速成!

    后台回复“入群”即可加入小z干货交流群
    
    展开全文
  •  #region 从datatable中将数据导出到excel  ///  /// 导出DataTable到流,此方法包含所有格式。  /// 2003 最大行数为65535,2007及以上为1048576  ///  /// <param name="dtSource"></param>  /// ...

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using NPOI;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.Formula.Eval;
    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using NPOI.XSSF.UserModel;
    using System.Web;

    namespace SZYH.Common
    {
        public class NpoiExcelHelper
        {
            public enum ExcelStyle
            {
                Office2003 = 0,
                Office2007AndAbove = 1
            }

            #region 从datatable中将数据导出到excel
            /// <summary>
            /// 导出DataTable到流,此方法包含所有格式。
            /// 2003 最大行数为65535,2007及以上为1048576
            /// </summary>
            /// <param name="dtSource"></param>
            /// <param name="strHeaderText"></param>
            /// <param name="excellStyle"></param>
            /// <returns></returns>
            public static NpoiMemoryStream ExportDt(DataTable dtSource, string strHeaderText, ExcelStyle excellStyle = ExcelStyle.Office2007AndAbove)
            {
                IWorkbook workbook = new XSSFWorkbook();
                if (excellStyle == ExcelStyle.Office2003)
                    workbook = new HSSFWorkbook();
                //
                var sheet = workbook.CreateSheet();
                //
                //
                var dateStyle = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                //取得列宽
                var arrColWidths = new int[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    arrColWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
                }
                for (var i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (var j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidths[j])
                        {
                            arrColWidths[j] = intTemp;
                        }
                    }
                }
                var rowIndex = 0;
                var maxRows = excellStyle == ExcelStyle.Office2003 ? 65535 : 1048576;
                foreach (DataRow row in dtSource.Rows)
                {
                    #region 新建表,填充表头,填充列头,样式
                    if (rowIndex == maxRows || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 表头及样式
                        {
                            var headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(strHeaderText);
                            var headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            var font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                            //headerRow.Dispose();
                        }
                        #endregion
                        #region 列头及样式
                        {
                            var headerRow = sheet.CreateRow(1);
                            var headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            var font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            foreach (DataColumn column in dtSource.Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                                //设置列宽
                                sheet.SetColumnWidth(column.Ordinal, (arrColWidths[column.Ordinal] + 1) * 256);
                            }
                            //headerRow.Dispose();
                        }
                        #endregion
                        rowIndex = 2;
                    }
                    #endregion
                    #region 填充内容
                    var dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        var newCell = dataRow.CreateCell(column.Ordinal);
                        var drValue = row[column].ToString().Trim();
                        if (!string.IsNullOrEmpty(drValue))
                        {
                            switch (column.DataType.ToString())
                            {
                                case "System.String": //字符串类型
                                    //double result;
                                    //if (IsNumeric(drValue, out result))
                                    //{
                                    //    double.TryParse(drValue, out result);
                                    //    newCell.SetCellValue(result);
                                    //    break;
                                    //}
                                    newCell.SetCellValue(drValue);
                                    break;
                                case "System.DateTime": //日期类型
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle; //格式化显示
                                    break;
                                case "System.Boolean": //布尔型
                                    bool boolV;
                                    bool.TryParse(drValue, out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16": //整型

                                case "System.Int32":

                                case "System.Int64":

                                case "System.Byte":
                                    int intV;
                                    int.TryParse(drValue, out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal": //浮点型

                                case "System.Double":
                                    double doubV;
                                    double.TryParse(drValue, out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull": //空值处理
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                        }
                        else
                        {
                            newCell.SetCellValue("");
                        }
                    }
                    #endregion
                    rowIndex++;
                }

                using (var ms = new NpoiMemoryStream())
                {
                    ms.AllowClose = false;
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    //sheet.Dispose();
                    //workbook.Dispose();
                    ms.AllowClose = true;
                    return ms;
                }
            }

            /// <summary>
            /// DataTable导出到Excel文件
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            /// <param name="strFileName">保存位置</param>
            public static bool ExportDTtoExcel2003(DataTable dtSource, string strHeaderText, string strFileName)
            {
                try
                {
                    using (var ms = ExportDt(dtSource, strHeaderText, ExcelStyle.Office2003))
                    {
                        using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                        {
                            var data = ms.ToArray();
                            fs.Write(data, 0, data.Length);
                            fs.Flush();
                        }
                    }
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }

            /// <summary>
            /// DataTable导出到Excel文件,格式为2007及以上格式
            /// </summary>
            /// <param name="dtSource">源DataTable</param>
            /// <param name="strHeaderText">表头文本</param>
            /// <param name="strFileName">保存位置</param>
            public static bool ExportDtToExcel(DataTable dtSource, string strHeaderText, string strFileName)
            {
                try
                {
                    using (var ms = ExportDt(dtSource, strHeaderText))
                    {
                        using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                        {
                            var data = ms.ToArray();
                            fs.Write(data, 0, data.Length);
                            fs.Flush();
                        }
                    }
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }

            #endregion

            #region 从excel中将数据导出到datatable

            /// <summary>读取excel
            /// 默认第一行为标头
            /// </summary>
            /// <param name="strFileName">excel文档路径</param>
            /// <returns></returns>
            public static DataTable ImportExceltoDt(string strFileName)
            {
                IWorkbook workbook;
                using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    workbook = WorkbookFactory.Create(file);
                var sheet = workbook.GetSheetAt(0);
                var dt = ImportDt(sheet, 0, true);
                return dt;
            }

            /// <summary>
            /// 读取excel
            /// </summary>
            /// <param name="strFileName">excel文件路径</param>
            /// <param name="sheetName">需要导出的sheet</param>
            /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
            /// <returns></returns>
            public static DataTable ImportExceltoDt(string strFileName, string sheetName, int headerRowIndex)
            {
                IWorkbook workbook;
                using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    workbook = WorkbookFactory.Create(file);
                var sheet = workbook.GetSheet(sheetName);
                var table = ImportDt(sheet, headerRowIndex, true);
                return table;
            }

            /// <summary>
            /// 读取excel
            /// </summary>
            /// <param name="strFileName">excel文件路径</param>
            /// <param name="sheetIndex">需要导出的sheet序号</param>
            /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
            /// <returns></returns>
            public static DataTable ImportExceltoDt(string strFileName, int sheetIndex, int headerRowIndex)
            {
                IWorkbook workbook;
                using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    workbook = WorkbookFactory.Create(file);
                ISheet sheet = workbook.GetSheetAt(sheetIndex);
                var table = ImportDt(sheet, headerRowIndex, true);
                return table;
            }

            /// <summary>
            /// 读取excel
            /// </summary>
            /// <param name="strFileName">excel文件路径</param>
            /// <param name="sheetName">需要导出的sheet</param>
            /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
            /// <param name="needHeader"></param>
            /// <returns></returns>
            public static DataTable ImportExceltoDt(string strFileName, string sheetName, int headerRowIndex, bool needHeader)
            {
                IWorkbook workbook;
                using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    workbook = WorkbookFactory.Create(file);
                var sheet = workbook.GetSheet(sheetName);
                var table = ImportDt(sheet, headerRowIndex, needHeader);
                return table;

            }

            /// <summary>
            /// 读取excel
            /// </summary>
            /// <param name="strFileName">excel文件路径</param>
            /// <param name="sheetIndex">需要导出的sheet序号</param>
            /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
            /// <param name="needHeader"></param>
            /// <returns></returns>
            public static DataTable ImportExceltoDt(string strFileName, int sheetIndex, int headerRowIndex, bool needHeader)
            {
                IWorkbook workbook;
                using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    workbook = WorkbookFactory.Create(file);
                var sheet = workbook.GetSheetAt(sheetIndex);
                var table = ImportDt(sheet, headerRowIndex, needHeader);
                return table;
            }

            /// <summary>
            /// 将制定sheet中的数据导出到datatable中
            /// </summary>
            /// <param name="sheet">需要导出的sheet</param>
            /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
            /// <param name="needHeader"></param>
            /// <returns></returns>
            static DataTable ImportDt(ISheet sheet, int headerRowIndex, bool needHeader)
            {
                var table = new DataTable();
                IRow headerRow;
                int cellCount;
                try
                {
                    if (headerRowIndex < 0 || !needHeader)
                    {
                        if (headerRowIndex < 0)
                        {
                            headerRowIndex = 0;
                        }
                        headerRow = sheet.GetRow(headerRowIndex);
                        cellCount = headerRow.LastCellNum;
                        for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                        {
                            var column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }
                    }
                    else
                    {
                        headerRow = sheet.GetRow(headerRowIndex);
                        cellCount = headerRow.LastCellNum;
                        for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                        {
                            if (headerRow.GetCell(i) == null)
                            {
                                if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                                {
                                    var column = new DataColumn(Convert.ToString("重复列名" + i));
                                    table.Columns.Add(column);
                                }
                                else
                                {
                                    var column = new DataColumn(Convert.ToString(i));
                                    table.Columns.Add(column);
                                }
                            }
                            else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                            {
                                var column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                var column = new DataColumn(headerRow.GetCell(i).ToString());
                                table.Columns.Add(column);
                            }
                        }
                    }
                    var startRow = headerRowIndex + 1;
                    if (!needHeader)
                    {
                        startRow = headerRowIndex;//无表头
                    }
                    for (var i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        try
                        {
                            IRow row;
                            row = sheet.GetRow(i) ?? sheet.CreateRow(i);
                            var dataRow = table.NewRow();
                            for (int j = row.FirstCellNum; j <= cellCount; j++)
                            {
                                try
                                {
                                    if (row.GetCell(j) != null)
                                    {
                                        switch (row.GetCell(j).CellType)
                                        {
                                            case CellType.String:
                                                var str = row.GetCell(j).StringCellValue;
                                                dataRow[j] = !string.IsNullOrEmpty(str) ? str : null;
                                                break;
                                            case CellType.Numeric:
                                                if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                                    dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                                else
                                                    dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                                break;
                                            case CellType.Boolean:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                break;
                                            case CellType.Error:
                                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                break;
                                            case CellType.Formula:
                                                switch (row.GetCell(j).CachedFormulaResultType)
                                                {
                                                    case CellType.String:
                                                        var strFormula = row.GetCell(j).StringCellValue;
                                                        dataRow[j] = !string.IsNullOrEmpty(strFormula)
                                                            ? strFormula
                                                            : null;
                                                        break;
                                                    case CellType.Numeric:
                                                        dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                        break;
                                                    case CellType.Boolean:
                                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                        break;
                                                    case CellType.Error:
                                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                        break;
                                                    default:
                                                        dataRow[j] = "";
                                                        break;
                                                }
                                                break;
                                            default:
                                                dataRow[j] = "";
                                                break;
                                        }
                                    }
                                }
                                catch
                                {
                                    //wl.WriteLogs(exception.ToString());
                                }
                            }
                            table.Rows.Add(dataRow);
                        }
                        catch (Exception exception)
                        {
                            //wl.WriteLogs(exception.ToString());
                        }
                    }
                }
                catch (Exception exception)
                {
                    //wl.WriteLogs(exception.ToString());
                }
                return table;
            }

            #endregion

            #region Public Methodes
            public static bool IsNumeric(String message, out double result)
            {
                var rex = new Regex(@"^[-]?\d+[.]?\d*$");
                result = -1;
                if (rex.IsMatch(message))
                {
                    result = double.Parse(message);
                    return true;
                }
                return false;
            }

            /// <summary>
            /// 获取Sheet个数
            /// </summary>
            /// <param name="outputFile"></param>
            /// <returns></returns>
            public static int GetSheetCount(string outputFile)
            {
                var count = 0;
                try
                {
                    var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                    var workbook = WorkbookFactory.Create(readfile);
                    count = workbook.NumberOfSheets;
                }
                catch (Exception exception)
                {
                    //wl.WriteLogs(exception.ToString());
                }
                return count;
            }

            /// <summary>
            /// 获取Sheet名称列表
            /// </summary>
            /// <param name="outputFile"></param>
            /// <returns></returns>
            public static IList<string> GetSheetNames(string outputFile)
            {
                IList<string> sheetNameList = new List<string>();
                try
                {
                    var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                    var workbook = WorkbookFactory.Create(readfile);
                    for (var i = 0; i < workbook.NumberOfSheets; i++)
                        sheetNameList.Add(workbook.GetSheetName(i));
                }
                catch (Exception exception)
                {
                    //wl.WriteLogs(exception.ToString());
                }
                return sheetNameList;
            }

            /// <summary>
            /// 保存Excel文件
            /// <para>Excel的导入导出都会在服务器生成一个文件</para>
            /// <para>路径:UpFiles/ExcelFiles</para>
            /// </summary>
            /// <param name="file">传入的文件对象</param>
            /// <returns>如果保存成功则返回文件的位置;如果保存失败则返回空</returns>
            public static string SaveUpLoadFile(HttpPostedFile file)
            {
                try
                {
                    var fileName = file.FileName.Insert(file.FileName.LastIndexOf('.'), "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                    if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/PC/ReportFile/UpLoadFile")))
                    {
                        Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/PC/ReportFile/UpLoadFile"));
                    }
                    var filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/PC/ReportFile/UpLoadFile"), fileName);
                    string directoryName = Path.GetDirectoryName(filePath);
                    file.SaveAs(filePath);
                    return filePath;
                }
                catch
                {
                    return string.Empty;
                }
            }
            #endregion
        }

        public class NpoiMemoryStream : MemoryStream
        {
            public NpoiMemoryStream()
            {
                AllowClose = true;
            }

            public bool AllowClose { get; set; }

            public override void Close()
            {
                if (AllowClose)
                    base.Close();
            }
        }
    }
     

    展开全文
  • 用Python自动生成Excel报表

    千次阅读 2020-11-06 10:04:25
    在日常工作中,可能会有一些重复无聊的任务,比如说,从 Excel 或数据库中收集一些数据,设置相应的数据格式并做成报表。 类似这种重复无聊的任务,我们完全可以交给 Python 去自动完成,只要第一次把 Python 代码写...

    在日常工作中,可能会有一些重复无聊的任务,比如说,从 Excel 或数据库中收集一些数据,设置相应的数据格式并做成报表。
    类似这种重复无聊的任务,我们完全可以交给 Python 去自动完成,只要第一次把 Python 代码写好,以后就可以一键完成,省时省力,还不容易出错,这是提升工作质量和工作效率的利器,让我们能够腾出时间和精力,去做更有价值的事情。
    1. 安装和导入模块
    以 Python 中的 openpyxl 模块为例,它能够读取和修改 Excel 文件,如果你还没有安装,可以通过以下命令进行安装:

    pip install openpyxl
    

    要测试 openpyxl 是否正确安装,可以在 Jupyter Lab 中运行以下代码:

    # 导入库
    import openpyxl
    # 查看版本
    openpyxl.__version__
    

    如果该模块正确安装,那么会输出版本号,假如你在使用过程中遇到问题,可以查阅官方文档。
    2. 读取和处理数据
    为了演示用 Python 自动生成 Excel 报表,我从网上找了一个数据集,是一家跨国公司的 54 万多行在线零售业务的交易数据,你可以进入公众号「林骥」的后台,回复「零售」两个字,获取该数据集的完整下载链接。
    把这个数据文件保存到代码上级目录的 data 文件夹,然后用 Pandas 读取它:

    # 读取数据
    import pandas as pd
    df = pd.read_excel('../data/Online Retail.xlsx')
    
    df
    

    在这里插入图片描述
    其中每一列代表的含义如下:

    InvoiceNo:发票编号
    StockCode:产品代码
    Description:产品名称
    Quantity:产品数量
    InvoiceDate:开票时间
    UnitPrice:产品单价
    CustomerID:客户编号
    Country:国家名称

    为了统计每天的销售额,我们先在数据中增加两列:日期和销售额,然后用函数实现汇总:

    # 从时间列中提取日期
    df['日期'] = df.InvoiceDate.dt.to_period('D').astype(str)
    
    # 计算销售额
    df['销售额'] = df.Quantity * df.UnitPrice
    
    # 汇总每天的销售额
    df_daily = pd.DataFrame(df.groupby('日期')['销售额'].agg('sum')).reset_index()
    
    df_daily
    

    在这里插入图片描述
    3. 设置和保存报表
    接下来,我们对表格进行相应的设置,包括:重命名工作表、把数据写入工作表、自定义标题和表格边框样式、设置行高和列宽、不显示网格线、冻结窗格、自动筛选、设置日期和数字格式等等。

    from openpyxl.utils.dataframe import dataframe_to_rows
    from openpyxl.styles import Font, Color, NamedStyle, Border, Side, PatternFill, Alignment, numbers
    
    # 创建工作簿
    wb = openpyxl.Workbook()
    
    # 激活工作表
    ws = wb.active
    
    # 重命名工作表
    ws.title = '每日销售额'
    
    # 把数据写入工作表
    for row in dataframe_to_rows(df_daily, index=False, header=True):
        ws.append(row)
        
    # 创建自定义的标题样式
    mytitle = NamedStyle(name='mytitle')
    mytitle.font = Font(bold=True, size=11, color='FFFFFF')
    bd = Side(style='thin', color='A6A6A6')
    mytitle.border = Border(left=bd, top=bd, right=bd, bottom=bd)
    mytitle.fill = PatternFill('solid', fgColor='00589F')
    mytitle.alignment = Alignment(horizontal='left', vertical='center')
    wb.add_named_style(mytitle)
    
    # 创建自定义表格边框样式
    myborder = NamedStyle(name='myborder')
    myborder.font = Font(bold=False, size=11, color='000000')
    bd = Side(style='thin', color='A6A6A6')
    myborder.border = Border(left=bd, top=bd, right=bd, bottom=bd)
    myborder.alignment = Alignment(vertical='center')
    wb.add_named_style(myborder)
    
    # 应用标题样式
    for cell in ws[1]:
        cell.style = mytitle
        
    # 对表格区域加边框
    from openpyxl.utils import get_column_letter, column_index_from_string
    table_range = ws['A2:' + get_column_letter(ws.max_column) + str(ws.max_row)]
    for row in table_range:
        for cell in row:
            cell.style = myborder
        
    # 设置行高和列宽
    ws.row_dimensions[1].height = 26
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 12
    
    # 设置不显示网格线
    ws.views.sheetView[0].showGridLines = False
    
    # 冻结窗格
    ws.freeze_panes = 'A3'
    
    # 自动筛选
    ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + str(ws.max_row)
    
    # 设置对齐格式
    for cell in ws['A']:
        cell.alignment = Alignment(horizontal='center', vertical='center')
    
    # 设置日期格式
    for cell in ws['A']:
        cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2
        
    # 设置数字格式
    for cell in ws['B']:
        cell.number_format = numbers.BUILTIN_FORMATS[3]
    

    最后,保存自动生成的 Excel 报表文件:

    # 保存为新的表格
    wb.save('../data/每日销售报表.xlsx')
    

    打开这个新生成的 Excel 报表文件,其中的内容如下:
    在这里插入图片描述
    小结
    本文介绍了用 Python 自动生成 Excel 报表的一种方法,从 openpyxl 模块的安装和导入,到读取和处理数据,再到设置和保存报表,只要你把数据源放在合适的位置,就能在 Jupyter Lab 中一键运行,自动生成相应的报表。

    参考链接 :

    用Python自动生成Excel报表 :https://mp.weixin.qq.com/s/niZ1_ORP5QRJ9xR-oR0wpw

    展开全文
  • 用c# 把excel数据自动导入sql数据库 并根据excel的内容自动创建数据库表,经过网上查资料和自己的努力做了出来 ,只不过是实现了功能并未优化代码,下面分享我的代码 首先建一个网站项目 ,其.aspx代码如下 ...
  • 内容索引:VB源码,报表打印,报表导入 以前帮朋友做的,财政局数据下发并生成报表,然后下发到各分局再进行导入,VB代码实现,中转出的数据格式主要是TXT文本文件,对于初学文本控制和EXCEL报表导出的有一定帮助,...
  • 1.项目中经常需要用到报表生成,信息导入数据库的功能.主要有以下几种. 2.其中比较简单的是 外部数据无需处理...3.选择导入数据源及相关表位置. 4.选择导入xlsx数据源位置,从第几行开始导入,字段名行的相对行位置...
  • 从MySQL中查询数据并且保存生成excel中 上一篇文中讲到: python批量爬取古诗词并保存之Excel或数据库mysql https://blog.csdn.net/weixin_44404350/article/details/116242727?spm=1001.2014.3001.5501 例如:...
  • 项目中常用到将数据导入Excel,将Excel中的数据导入数据库的功能,曾经也查找过相关的内容,将曾经用过的方案总结一下。 方案一  NPOI   NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD...
  • [Asp.net]常见数据导入ExcelExcel数据导入数据库解决方案,总有一款适合你! 原文:[Asp.net]常见数据导入ExcelExcel数据导入数据库解决方案,总有一款适合你!引言 项目中常用到将数据导入Excel...
  • 如何利用excel中的数据源制作数据地图

    万次阅读 多人点赞 2016-07-13 12:40:27
    利用excel中的数据源制作数据地图的方法以不新奇,可以直接利用excel制作,也可以利用插件和其他软件来实现。本文就从这三方面来谈谈如何利用制作数据源地图。
  • 首先看下柱状图生成的效果图: 一、微软Excel中图表生成Excel生成这样的柱状图需要如下步骤: 1.首先需要在单元格中填写图表数据源所需要数据。...5.为图表指定数据源并选择生成的系列是按行还是按列。 ...
  • 项目中常用到将数据导入Excel,将Excel中的数据导入数据库的功能,曾经也查找过相关的内容,将曾经用过的方案总结一下。 方案一 NPOI NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软...
  • 1.首先需要在单元格中填写图表数据源所需要数据。如上图中单元格数据Employee,Salary,Other等。 2.填写完数据后,点击Excel工具栏中图表按钮。 3.点击图表按钮后,会弹出图表导向的窗口,选择你需要生成的...
  • 1.首先需要在单元格中填写图表数据源所需要数据。如上图中单元格数据Employee,Salary,Other等。 2.填写完数据后,点击Excel工具栏中图表按钮。 3.点击图表按钮后,会弹出图表导向的窗口,选择你需要生成的...
  • 项目中常用到将数据导入Excel,将Excel中的数据导入数据库的功能,曾经也查找过相关的内容,将曾经用过的方案总结一下。 方案一 NPOI NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2...
  • Asp.net程序中用NPOI生成标准Excel报表导入导出一应俱全 上班第一份工作就是做程序数据导入导出,听起来似乎挺Easy的,开始我也这样认为,但是在实际操作中却不同了...  以往的导出数据是用HTML标签拼接成...
  • 本期课程,我们将借助服务端表格组件GcExcel 的模板语法来完成Excel报表生成,通过使用简单的语法标记,可以在不借助任何第三方组件的情况下,结合数据源自动化地生成Excel模板文件。 目前,GcExcel可以生成的...
  • 首先看下生成的效果图(即使没有安装Excel也能生成这样的图表)。 其中这个图表中系列有柱状图也有线形图,如何能快速简单生成这样合并图表呢? 下面来看下为生成这样图表要编写的代码: 1 /// <summary&...
  • 如何使用Excel表格导入数据批量开票

    千次阅读 2017-10-28 20:01:50
    首先我来说一下它是做什么用的,它的主要作用是把用户的开票数据Excel数据、ERP 系统导入到税控盘开票软件中。 解决企业开票明细多、开票数据量大、手工输入明细慢、开票赋码不方便等问题,通过使用开票助手...
  • 大部分的数据分析师都或多或少掉入这样的陷阱:每天大部分的工作都花在查数上,干着干着变成了“查数菇”。看上去帮老板或其他同事查数据数据分析师天经地义的任务,怎么会成为陷阱呢?我来给你分析分析: 业务...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,225
精华内容 1,690
关键字:

导入数据源excel自动生成报表