在C#中根据Excel文件画FastReport .NET报表模板

 用NPOI改写了读取EXCEL的部分，然后加入到FP的源码( http://www.commuch.com )中去以插件的形式存在。如图

    public void LoadReport(string fileName)
        {

            WorkBook book = new WorkBook(fileName);
            List<string> sheetNames = new List<string>();
            for (int i = 0; i < book.SheetCount; i++) {
                sheetNames.Add(book[i].Name);
            }
            string LeadOutPath = string.Empty;
            List<string> SelectedNames = new List<string>();
            using (FormSheetSelect frm = new FormSheetSelect().Start(sheetNames)) {
                if (frm.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
                    return ;
                LeadOutPath = frm.LeadOutPath;
                SelectedNames.AddRange(frm.SelectedItems);
            }


            for (int i = 0; i < book.SheetCount; i++) {
                if (!SelectedNames.Contains(book[i].Name))
                    continue;
                IWorkSheet sheet = book[i];
                if (sheet.RowCount < 1)
                    continue;

                System.IO.StringWriter mem = new System.IO.StringWriter();
                System.Xml.XmlTextWriter sw = new System.Xml.XmlTextWriter(mem);
                sw.Formatting = System.Xml.Formatting.Indented;
                sw.WriteStartElement("ReportPage");
                sw.WriteAttributeString("Name", "Page1");
                #region 处理纸张

                System.Drawing.Size paperSize = new System.Drawing.Size();
                sw.WriteAttributeString("RawPaperSize", sheet.PageSettings.PaperSize.RawKind.ToString());
                paperSize = new System.Drawing.Size(Inch2Pix(sheet.PageSettings.PaperSize.Width / 100, true), Inch2Pix(sheet.PageSettings.PaperSize.Height / 100, false));
                if (sheet.PageSettings.Landscape) {
                    sw.WriteAttributeString("Landscape", "true");
                    sw.WriteAttributeString("PaperWidth", GetPaperSize(sheet.PageSettings.PaperSize).Height.ToString());
                    sw.WriteAttributeString("PaperHeight", GetPaperSize(sheet.PageSettings.PaperSize).Width.ToString());
                }
                else {
                    sw.WriteAttributeString("PaperWidth", GetPaperSize(sheet.PageSettings.PaperSize).Width.ToString());
                    sw.WriteAttributeString("PaperHeight", GetPaperSize(sheet.PageSettings.PaperSize).Height.ToString());

                }
                //bound
                sw.WriteAttributeString("LeftMargin", Inch2mm(sheet.PageSettings.Margins.Left / 100d).ToString());
                sw.WriteAttributeString("TopMargin", Inch2mm(sheet.PageSettings.Margins.Top / 100d).ToString());
                sw.WriteAttributeString("RightMargin", Inch2mm(sheet.PageSettings.Margins.Right / 100d).ToString());
                sw.WriteAttributeString("BottomMargin", Inch2mm(sheet.PageSettings.Margins.Bottom / 100d).ToString());

                #endregion

                #region 写标题
                sw.WriteStartElement("ReportTitleBand");
                sw.WriteAttributeString("Name", "ReportTitle1");
                if (sheet.PageSettings.Landscape) {

                    sw.WriteAttributeString("Height", (paperSize.Width - (sheet.PageSettings.Margins.Left + sheet.PageSettings.Margins.Right)).ToString());
                    sw.WriteAttributeString("Width", (paperSize.Height - (sheet.PageSettings.Margins.Top + sheet.PageSettings.Margins.Bottom)).ToString());
                }
                else {
                    sw.WriteAttributeString("Width", (paperSize.Width - (sheet.PageSettings.Margins.Left + sheet.PageSettings.Margins.Right)).ToString());
                    sw.WriteAttributeString("Height", (paperSize.Height - (sheet.PageSettings.Margins.Top + sheet.PageSettings.Margins.Bottom)).ToString());

                }
                sw.WriteStartElement("TableObject");
                sw.WriteAttributeString("Name", "Table2");
                if (sheet.PageSettings.Landscape) {

                    sw.WriteAttributeString("Height", (paperSize.Width - (sheet.PageSettings.Margins.Left + sheet.PageSettings.Margins.Right)).ToString());
                    sw.WriteAttributeString("Width", (paperSize.Height - (sheet.PageSettings.Margins.Top + sheet.PageSettings.Margins.Bottom)).ToString());
                }
                else {
                    sw.WriteAttributeString("Width", (paperSize.Width - (sheet.PageSettings.Margins.Left + sheet.PageSettings.Margins.Right)).ToString());
                    sw.WriteAttributeString("Height", (paperSize.Height - (sheet.PageSettings.Margins.Top + sheet.PageSettings.Margins.Bottom)).ToString());

                }
                //sw.WriteAttributeString("ManualBuildEvent", "Table2_ManualBuild");
                sw.WriteAttributeString("AfterDataEvent", "Table2_AfterData");

                #endregion
                double pi = 1;
                try {
                    //指定了缩放条件则缩放
                    pi = (int)sheet.Scale / 100d;
                }
                catch {
                    pi = 1;
                }
                //认为表单是规则的二维表
                IRow firstRow = sheet.FirstRow;
                int cellCount = firstRow.CellCount;//强制以标题占用的单元格数目为准
                if (firstRow.FirstCell != null) {
                    System.Drawing.Point firtCellPoint = firstRow.FirstCell.MergeCellsCount;
                    if (firtCellPoint.Y > 0) {
                        cellCount = firtCellPoint.Y;
                    }
                }
                for (int c = firstRow.FirstCellIndex; c < cellCount + firstRow.FirstCellIndex; c++) {
                    sw.WriteStartElement("TableColumn");
                    sw.WriteAttributeString("Name", "Column" + c.ToString());
                    sw.WriteAttributeString("Width", (pi * sheet.GetColumnWidth(c)).ToString());
                    sw.WriteEndElement();
                }
                
                    for (int r = sheet.FirstRowIndex; r < sheet.RowCount + sheet.FirstRowIndex; r++) {
                    firstRow = sheet.Rows(r);
                    if (firstRow == null)
                        continue;
                    sw.WriteStartElement("TableRow");
                    sw.WriteAttributeString("Name", "Row" + r.ToString());
                    sw.WriteAttributeString("Height", Point2Pix(pi * firstRow.Height).ToString());


                    for (int c = sheet.Rows(r).FirstCellIndex; c <= cellCount + firstRow.FirstCellIndex; c++) {

                        ICell cell = firstRow[c];
                        if (cell == null)
                            continue;
                        sw.WriteStartElement("TableCell");
                        sw.WriteAttributeString("Name", "Cell" + (c * 1000 + r).ToString());
                        if (cell.Text != null) {
                            sw.WriteStartAttribute("Text");
                            double d;
                            if (double.TryParse(cell.Text.ToString(), out d) && d == 0) {
                                sw.WriteValue("");
                            }
                            else sw.WriteValue(cell.Text);
                            sw.WriteEndAttribute();
                        }
                        if (cell.TextOrientation)
                            sw.WriteAttributeString("TextOrientation", "True");
                        sw.WriteAttributeString("HideZeros", "true");
                        sw.WriteStartAttribute("HorzAlign");
                        sw.WriteValue(cell.HorizontalAlignment.ToString());

                        sw.WriteEndAttribute();
                        sw.WriteAttributeString("VertAlign", cell.VerticalAlignment.ToString());
                        sw.WriteAttributeString("Font", cell.Font(pi));
                        System.Drawing.Point MergeCellsCount = cell.MergeCellsCount;
                        if (MergeCellsCount.Y > 1)
                            sw.WriteAttributeString("ColSpan", MergeCellsCount.Y.ToString());
                        if (MergeCellsCount.X > 1)
                            sw.WriteAttributeString("RowSpan", MergeCellsCount.X.ToString());

                        Type dataType = cell.DataType;
                        if (dataType.Equals(typeof(double))) {
                            sw.WriteAttributeString("Format", "Number");
                            sw.WriteAttributeString("Format.UseLocale", "false");
                            sw.WriteAttributeString("Format.DecimalDigits", "2");
                            sw.WriteAttributeString("Format.DecimalSeparator", ".");
                            sw.WriteAttributeString("Format.GroupSeparator", "");
                            sw.WriteAttributeString("Format.NegativePattern", "1");
                        }
                        else
                            if (dataType.Equals(typeof(int))) {
                                sw.WriteAttributeString("Format", "Number");
                                sw.WriteAttributeString("Format.UseLocale", "false");
                                sw.WriteAttributeString("Format.DecimalDigits", "0");
                                sw.WriteAttributeString("Format.DecimalSeparator", ".");
                                sw.WriteAttributeString("Format.GroupSeparator", "");
                                sw.WriteAttributeString("Format.NegativePattern", "1");
                                break;
                            }



                        string line = string.Format("{0}{1}{2}{3}",
                                    cell.HasBorder(0) ? ",Left" : "",
                                    (MergeCellsCount.Y > 1) ? (firstRow[c + MergeCellsCount.Y - 1].HasBorder(1) ? ",Right" : "") : (cell.HasBorder(1) ? ",Right" : ""),
                                    cell.HasBorder(2) ? ",Top" : "",
                                    (MergeCellsCount.X > 1) ? (sheet[r + MergeCellsCount.X - 1, c].HasBorder(3) ? ",Bottom" : "") : (cell.HasBorder(3) ? ",Bottom" : "")

                                  );


                        if (line.Length > 0)
                            sw.WriteAttributeString("Border.Lines", line.Substring(1));


                        sw.WriteEndElement();



                    }
                    sw.WriteEndElement();
                }

                sw.WriteEndElement();
                sw.WriteEndElement();
                sw.WriteEndElement();

                mem.Flush();
                if (string.IsNullOrEmpty(LeadOutPath)) {
                     Report.Load(
                         new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(
                         template.Replace("$#report#{1}quot;, mem.ToString().Replace("
", "
")))));
                }
                else
                {
                    if (!System.IO.Directory.Exists(System.IO.Path.Combine(LeadOutPath,System.IO.Path.GetFileNameWithoutExtension( book.FullName))))
                        System.IO.Directory.CreateDirectory(System.IO.Path.Combine(LeadOutPath, System.IO.Path.GetFileNameWithoutExtension(book.FullName)));

                    System.IO.File.WriteAllText(
                                       System.IO.Path.Combine(LeadOutPath, System.IO.Path.GetFileNameWithoutExtension(book.FullName) + "\\" + sheet.Name + ".frx"),
                                       template.Replace("$#report#{1}quot;, mem.ToString().Replace("
", "
")), System.Text.Encoding.UTF8);
                }
                sw.Close();
                mem.Close();
                if (string.IsNullOrEmpty(LeadOutPath))
                    break;//非导出模式只处理第一个表单

            }


            book.Close();

            if (!string.IsNullOrEmpty(LeadOutPath)) {
                System.Windows.Forms.MessageBox.Show("ok");

            }
        }

转换的报表几乎与原表一样，总算不再需要FP来画表了，画表永远 是EXCEL最爽。

以下为EXCEL中的效果