Crystal Reports for Visual Studio 2010

• http://www.sdn.sap.com/irj/sdn/crystalreports-dotnetBottom of the page you'll find all the downloads including Initial installer and deployment packages and SDK help files:
  

  Download Crystal Reports for Visual Studio 2010

  • Complete Package (EXE)
  • Click Once
  • Merge Modules
  • Redist Installation (32 bit)
  • Redist Installation (64 bit)

  Getting Started with Crystal Reports for Visual Studio 2010

  • What's New
  • Licensing
  • Supported Platforms
  • Sample Applications
  • SDK Library

  Tutorials for Crystal Reports for Visual Studio 2010 »

  • Overview Viewlet
  • Sorting and Filtering Viewlet
  • Dynamic Grouping Viewlet
  • XLSX Export Viewlet

  Related Areas

  • Crystal Reports Design
  • Crystal Reports for Eclipse
  • .NET Articles

  Service Pack 1:

  Crystal Reports for Visual Studio .NET 2010 - Support Pack 1

  • Support Pack 1
  • Redist Install 32bit
  • Redist Install 64bit
  • Merge Modules
  • ClickOnce

SQL: MERGE statement with Trigger

Trigger Implementation

For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.

If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

Please refer to:

http://msdn.microsoft.com/en-us/library/bb510625.aspx

How to Find Duplicates in Excel

Please refer to
http://www.wikihow.com/Find-Duplicates-in-Excel

SQL - Delete Duplicated Records

WITH CTE AS(
   SELECT *,
       RN = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY CreatedDate Desc)
   FROM dbo.dw_emp
)
DELETE FROM CTE WHERE RN > 1

Table dw_emp Structure
------------------------------------------------------------------------------------------------

Code	Name	Address	CreatedDate
10001	Mark	Tai Seng Street	1/12/2012
10002	Jack	Jurong East St 11	12/12/2011
10001	Mark	Tai Seng Street	12/5/2013

If need delete the duplicated one, and remain the one whose created date is biggest, you can use the script above.

Excel 2007: Highlight Duplicates

Using conditional formatting, you can easily highlight duplicate values in your spreadsheet. This will make it easier to identify the duplicates so that you can remove them. To do this…

1. Select the column that is to be checked for duplicate values.

2. Go to the Ribbon and select the Home tab.

3. In the Styles section, click Conditional Formatting.

4. Select Highlight Cells Rules and click Duplicate Values.

5. In the Duplicate Values dialog box, choose the formatting you wish to use from the dropdown. The default is a light red fill with dark red text.

6. Click OK.

The background color of all values that appear more than once in the selected column will show the selected formatting.

OPENXML: Insert Multiple images into MS Excel file

On the web, found 2 good blog about insert image into MS Excel file.
1. How to insert an image in Excel Open XML
2. How to insert multiple images in Excel Open XML

Method 1 is very good, but it doesn't support multiple images.
Method 2 is also good, but it cannot specify the start cell to insert.

After some research work, I modified the source code based on method 1, to let it can support multiple images.

The code:

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace WindowsFormsApplication2
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public static string sourceFile = @"D:\Projects\myExcel\Travel.xlsx";
        public static string targetFile = @"C:\Temp\Test.xlsx";
        public static string ImageFile = @"C:\Temp\DSC_7328.JPG";

        private void button1_Click(object sender, EventArgs e)
        {
            File.Copy(sourceFile, targetFile, true);

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(targetFile, true))
            {
                WorkbookPart workbookpart = document.WorkbookPart;
                //WorksheetPart sheet1 = workbookpart.WorksheetParts.First();
                WorksheetPart sheet1 = GetSheetByName(workbookpart, "sheet1");
               
                //insert Image by specifying two range
                InsertImage(sheet1, 1, 1, 1, 1, new FileStream(ImageFile, FileMode.Open));
                InsertImage(sheet1, 2, 1, 2, 1, new FileStream(ImageFile, FileMode.Open));
                document.WorkbookPart.Workbook.Save();
                // Close the document handle.
                document.Close();
            }
            System.Diagnostics.Process.Start(targetFile);
        }

        /// <summary>
        /// Inserts the image at the specified location
        /// </summary>
        /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
        /// <param name="startRowIndex">The starting Row Index</param>
        /// <param name="startColumnIndex">The starting column index</param>
        /// <param name="endRowIndex">The ending row index</param>
        /// <param name="endColumnIndex">The ending column index</param>
        /// <param name="imageStream">Stream which contains the image data</param>
        private void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
        {
            ImagePartType ipt=ImagePartType.Jpeg;
            DrawingsPart drawingsPart1;
            ImagePart imagePart1;
            WorksheetDrawing worksheetDrawing1;
            if (sheet1.DrawingsPart == null)
            {
                drawingsPart1 = sheet1.AddNewPart<DrawingsPart>();
                imagePart1 = drawingsPart1.AddImagePart(ipt, sheet1.GetIdOfPart(drawingsPart1));
                worksheetDrawing1 = new WorksheetDrawing();
            }
            else
            {
                drawingsPart1 = sheet1.DrawingsPart;
                imagePart1 = drawingsPart1.AddImagePart(ipt);
                drawingsPart1.CreateRelationshipToPart(imagePart1);
                worksheetDrawing1 = drawingsPart1.WorksheetDrawing;
            }
           
            int imageNumber = drawingsPart1.ImageParts.Count<ImagePart>();
            if (imageNumber == 1)
            {
                Drawing drawing = new Drawing();
                drawing.Id = drawingsPart1.GetIdOfPart(imagePart1);
                sheet1.Worksheet.Append(drawing);
            }
            imagePart1.FeedData(imageStream);
           
            Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };

            Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
            Xdr.ColumnId columnId1 = new Xdr.ColumnId();
            columnId1.Text = startColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
            columnOffset1.Text = "38100";
            Xdr.RowId rowId1 = new Xdr.RowId();
            rowId1.Text = startRowIndex.ToString();
            Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
            rowOffset1.Text = "0";

            fromMarker1.Append(columnId1);
            fromMarker1.Append(columnOffset1);
            fromMarker1.Append(rowId1);
            fromMarker1.Append(rowOffset1);

            Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
            Xdr.ColumnId columnId2 = new Xdr.ColumnId();
            columnId2.Text = endColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
            columnOffset2.Text = "542925";
            Xdr.RowId rowId2 = new Xdr.RowId();
            rowId2.Text = endRowIndex.ToString();
            Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
            rowOffset2.Text = "161925";

            toMarker1.Append(columnId2);
            toMarker1.Append(columnOffset2);
            toMarker1.Append(rowId2);
            toMarker1.Append(rowOffset2);

            Xdr.Picture picture1 = new Xdr.Picture();

            Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
            Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = new UInt32Value((uint)(1024 + imageNumber)), Name = "Picture " + imageNumber.ToString() };

            Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
            A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };

            nonVisualPictureDrawingProperties1.Append(pictureLocks1);

            nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
            nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);

            Xdr.BlipFill blipFill1 = new Xdr.BlipFill();

            A.Blip blip1 = new A.Blip() { Embed = drawingsPart1.GetIdOfPart(imagePart1), CompressionState = A.BlipCompressionValues.Print };
            blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();

            A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };

            A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
            useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");

            blipExtension1.Append(useLocalDpi1);

            blipExtensionList1.Append(blipExtension1);

            blip1.Append(blipExtensionList1);

            A.Stretch stretch1 = new A.Stretch();
            A.FillRectangle fillRectangle1 = new A.FillRectangle();

            stretch1.Append(fillRectangle1);

            blipFill1.Append(blip1);
            blipFill1.Append(stretch1);

            Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();

            A.Transform2D transform2D1 = new A.Transform2D();
            A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
            A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };

            transform2D1.Append(offset1);
            transform2D1.Append(extents1);

            A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
            A.AdjustValueList adjustValueList1 = new A.AdjustValueList();

            presetGeometry1.Append(adjustValueList1);

            shapeProperties1.Append(transform2D1);
            shapeProperties1.Append(presetGeometry1);

            picture1.Append(nonVisualPictureProperties1);
            picture1.Append(blipFill1);
            picture1.Append(shapeProperties1);
            Xdr.ClientData clientData1 = new Xdr.ClientData();

            twoCellAnchor1.Append(fromMarker1);
            twoCellAnchor1.Append(toMarker1);
            twoCellAnchor1.Append(picture1);
            twoCellAnchor1.Append(clientData1);

            worksheetDrawing1.Append(twoCellAnchor1);

            if (imageNumber == 1)
                drawingsPart1.WorksheetDrawing = worksheetDrawing1;

           
        }
        #region Helper methods
        /// <summary>
        /// Returns the WorksheetPart for the specified sheet name
        /// </summary>
        /// <param name="workbookpart">The WorkbookPart</param>
        /// <param name="sheetName">The name of the worksheet</param>
        /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
        private static WorksheetPart GetSheetByName(WorkbookPart workbookpart, string sheetName)
        {
            foreach (WorksheetPart sheetPart in workbookpart.WorksheetParts)
            {
                string uri = sheetPart.Uri.ToString();
                if (uri.EndsWith(sheetName + ".xml"))
                    return sheetPart;
            }
            return null;
        }
        #endregion Helper methods
    }
}

*********************************************************************************

In this code, I use a template MS excel file, not generate MS Excel from scratch.

Sample file after generated: Click here to download

OpenXML how to copy cells with Validation

It seems that blank cells with data validation (no value, only data validation) are not retaining the data validation after being copied to other place (e.g. another worksheet). As soon as I try to copy a cell with some value + data validation, everything gets copied correctly.

Scenario: 

If don't choose any value in the drop down list, when you copy the cell or the row to other place using code, the validation will not retain.
What I have done is put a space on top the values list (named range, refer to previous post for how to add named range, and how to put drop down list validation), and in my template, I choose the space value in it. and then when I copy the cell to other place, the validation is retained.

My Partial Code:

                var wb = new XLWorkbook(exportDest);
                var wsData = wb.Worksheet("Data");
                wb.NamedRange("CorpGrade").SetRefersTo("Data!$B$3:$B$15");
                wb.NamedRange("Category").SetRefersTo("Data!$D$3:$D$10");
                var ws = wb.Worksheet(2);
                int i = startRow;
                while (i < 100)
                {
                    ws.Row(i).InsertRowsAbove(1);
                    var row = ws.Row(i);
                    ws.Row(i + 1).CopyTo(row);
                    //var range = ws.Range(i + 1, 1, i + 1, 50).CopyTo(row.Cell(1));
                    i++;
                }

                wb.Save();