using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
namespace CShareExcelInterop
{
class Program
{
private static int lastRow=0;
private static System.Drawing.Color borderColor = System.Drawing.Color.Black;
private static System.Drawing.Color categoryInteriorColor = System.Drawing.Color.FromArgb(217, 217, 217);
static void Main(string[] args)
{
string fileName = "c:\\book1.xlsx";
var application = new Application();
application.Visible = true;
var workbook = application.Workbooks.Open(fileName);
var worksheet = workbook.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
createHeader(application);
createContentTable(application);
createBottom(application);
Console.ReadLine();
application.Quit();
}
static void createHeader(Application app)
{
string logo = "c:\\logo.gif";
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
sheet.Shapes.AddPicture(logo, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoCTrue, 5, 5, 215, 51);
sheet.Range["A1:A1"].EntireColumn.ColumnWidth = 40;
sheet.Range["A1:A1"].EntireRow.RowHeight = 60;
//sheet.Range["B1:D1"].MergeCells = true;
sheet.Range["B1:D1"].Merge(0);
sheet.Range["B1:D1"].EntireColumn.AutoFit();
sheet.Range["B1:D1"].Value = "QUOTATION";
sheet.Range["B1:D1"].Font.Size = 28;
sheet.Range["B1:D1"].Font.Name = "Arial Black";
System.Drawing.Color rgbColor = System.Drawing.Color.FromArgb(216, 228, 232);
sheet.Range["B1:D1"].Font.Color = System.Drawing.ColorTranslator.ToOle(rgbColor);
sheet.Range["B1:D1"].EntireColumn.ColumnWidth = float.Parse(sheet.Range["B1:D1"].EntireColumn.ColumnWidth.ToString()) + 5;
sheet.Range["A3"].Value = "SuiteA, 3rd Floor Room 1008, Yan Hong Building, Wan Hu West Street No22.";
sheet.Range["A4"].Value = "Xi Ba He, Chao Yang District, Beijing,100015";
sheet.Range["A5"].EntireColumn.AutoFit();
sheet.Range["A5"].Value = "Phone:010-51292800 Fax:ext 603 Mail： abc@abc.com";
sheet.Range["C3"].Value = "DATE:";
sheet.Range["C3"].Font.Size = 9;
sheet.Range["C3"].Font.Name = "Arial";
sheet.Range["C3"].Font.Bold = true;
sheet.Range["D3"].Font.Size = 9;
sheet.Range["D3"].Font.Name = "Arial";
sheet.Range["D3"].Font.Bold = true;
sheet.Range["C4"].Value = "QUOTATION #";
sheet.Range["C4"].Font.Size = 9;
sheet.Range["C4"].Font.Name = "Arial";
sheet.Range["C4"].Font.Bold = true;
sheet.Range["D4"].Font.Size = 9;
sheet.Range["D4"].Font.Name = "Arial";
sheet.Range["D4"].Font.Bold = true;
sheet.Range["A3"].Font.Size = 8;
sheet.Range["A3"].Font.Name = "Arial";
sheet.Range["A4"].Font.Size = 8;
sheet.Range["A4"].Font.Name = "Arial";
sheet.Range["A5"].Font.Size = 8;
sheet.Range["A5"].Font.Name = "Arial";
sheet.Range["A5"].EntireColumn.ColumnWidth = float.Parse(sheet.Range["A5"].EntireColumn.ColumnWidth.ToString());// +5;
sheet.Range["A7"].Value = "Bill To:";
sheet.Range["A8"].Value = "Beijing City International School";
sheet.Range["A9"].Value = "No.77 Baiziwan Nan Er Road Chaoyang District, Beijin";
sheet.Range["A10"].Value = "100022";
sheet.Range["A11"].Value = "Phone:010-87717171";
sheet.Range["A7"].Font.Size = 10;
sheet.Range["A7"].Font.Name = "Arial";
sheet.Range["A7"].Font.Bold = true;
sheet.Range["A8"].Font.Size = 8;
sheet.Range["A8"].Font.Name = "Arial";
sheet.Range["A9"].Font.Size = 8;
sheet.Range["A9"].Font.Name = "Arial";
sheet.Range["A10"].Font.Size = 8;
sheet.Range["A10"].Font.Name = "Arial";
sheet.Range["A10"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
sheet.Range["A11"].Font.Size = 8;
sheet.Range["A11"].Font.Name = "Arial";
lastRow=11;
}
static void createContentHeader(Application app)
{
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create content header
//sheet.Range["A13", "D13"].Merge(0);
System.Drawing.Color interiorColor = System.Drawing.Color.FromArgb(216, 228, 232);
sheet.Range["A13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
sheet.Range["B13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
sheet.Range["C13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
sheet.Range["D13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
sheet.Range["A13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
sheet.Range["B13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
sheet.Range["C13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
sheet.Range["D13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
sheet.Range["A13"].Value = "DESCRIPTION";
sheet.Range["A13"].Font.Size = 8;
sheet.Range["A13"].Font.Name = "Arial";
sheet.Range["A13"].Font.Bold = true;
sheet.Range["B13"].Value = "QTY";
sheet.Range["B13"].Font.Size = 8;
sheet.Range["B13"].Font.Name = "Arial";
sheet.Range["B13"].Font.Bold = true;
sheet.Range["C13"].Value = "UNIT PRICE";
sheet.Range["C13"].Font.Size = 8;
sheet.Range["C13"].Font.Name = "Arial";
sheet.Range["C13"].Font.Bold = true;
sheet.Range["D13"].Value = "SUBTOTAL";
sheet.Range["D13"].Font.Size = 8;
sheet.Range["D13"].Font.Name = "Arial";
sheet.Range["D13"].Font.Bold = true;
lastRow = 13;
}
static void createCategoryHeader(Application app)
{
lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create category
var range = sheet.Range["A" + lastRow, "D" + lastRow];
range.Merge(0);
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(categoryInteriorColor);
range.Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
range.Value = "******Category Name******";
range.Font.Size = 10;
range.Font.Name = "Arial";
range.Font.Bold = true;
}
static void createProduct(Application app,System.Data.DataRow dr)
{
lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create product
//var range = sheet.Range["A" + lastRow , "D" + lastRow ];
var range = sheet.Range["A" + lastRow];
range.Value = dr["FItemName"].ToString();
range.Font.Size = 10;
range.Font.Name = "Arial";
range = sheet.Range["B" + lastRow];
range.Value = dr["FAuxqtyFrom"].ToString();
range.Font.Size = 10;
range.Font.Name = "Arial";
range = sheet.Range["C" + lastRow];
range.Value = dr["FAuxTaxPrice"].ToString();
range.Font.Size = 10;
range.Font.Name = "Arial";
range = sheet.Range["D" + lastRow];
range.Value = Convert.ToDecimal(dr["FAuxTaxPrice"]) * Convert.ToDecimal(dr["FAuxqtyFrom"]);
range.Font.Size = 10;
range.Font.Name = "Arial";
/*A``
range = sheet.Range["A" + lastRow , "D" + lastRow ];
int startRow = range.Row;
long endRow = long.Parse(range.EntireRow.Rows.CountLarge.ToString());
int startColumn = range.Column;
int endColumn = int.Parse(range.EntireColumn.Columns.CountLarge.ToString());
Console.WriteLine("column:" + startColumn + " entircolumn:" + endColumn);
Console.WriteLine("row:" + startRow + " endRow:" + endRow);
* */
}
static void createDescription(Application app)
{
lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create Description
var range = sheet.Range["A" + lastRow , "D" + lastRow ];
range.Cells.Merge(true);
range.Value = "******Description******";
range.Font.Size = 10;
range.Font.Name = "Arial";
range.EntireRow.RowHeight = 60;
}
static void createSubTotal(Application app)
{
lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create subtotal
var range = sheet.Range["A" + lastRow, "C" + lastRow];
range.Cells.Merge(true);
range.Interior.Color = categoryInteriorColor;
range.Borders.Color = borderColor;
range.Value = "Subtotal";
range.Font.Bold = true;
range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
range = sheet.Range["D" + lastRow];
range.Borders.Color = borderColor;
range.Value = "0.00";
}
static void creTotal(Application app)
{
lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//create total
var range = sheet.Range["A" + lastRow, "C" + lastRow];
range.Cells.Merge(true);
range.Interior.Color = categoryInteriorColor;
range.Borders.Color = borderColor;
range.Value = "TOTAL";
range.Font.Bold = true;
range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
range = sheet.Range["D" + lastRow];
range.Borders.Color = borderColor;
range.Value = "0.00";
//lastRow = 18;
}
static void createContentTable(Application app)
{
//var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
createContentHeader(app);
createCategoryHeader(app);
System.Data.DataTable dt = DBHelper.GetProList("AQ000005");
for (int i = 0; i < dt.Rows.Count ; i++)
{
createProduct(app,dt.Rows[i]);
//if (i % 3 == 0)
//{
// createCategoryHeader(app);
// createProduct(app);
// createDescription(app);
//}
}
createSubTotal(app);
creTotal(app);
}
//static void createContentTable(Application app)
//{
// var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
// //create content header
// //sheet.Range["A13", "D13"].Merge(0);
// System.Drawing.Color interiorColor=System.Drawing.Color.FromArgb(216,228,232);
// sheet.Range["A13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
// sheet.Range["B13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
// sheet.Range["C13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
// sheet.Range["D13"].Interior.Color = System.Drawing.ColorTranslator.ToOle(interiorColor);
// System.Drawing.Color borderColor = System.Drawing.Color.Black;
// sheet.Range["A13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
// sheet.Range["B13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
// sheet.Range["C13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
// sheet.Range["D13"].Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
// sheet.Range["A13"].Value = "DESCRIPTION";
// sheet.Range["A13"].Font.Size = 8;
// sheet.Range["A13"].Font.Name = "Arial";
// sheet.Range["A13"].Font.Bold = true;
// sheet.Range["B13"].Value = "QTY";
// sheet.Range["B13"].Font.Size = 8;
// sheet.Range["B13"].Font.Name = "Arial";
// sheet.Range["B13"].Font.Bold = true;
// sheet.Range["C13"].Value = "UNIT PRICE";
// sheet.Range["C13"].Font.Size = 8;
// sheet.Range["C13"].Font.Name = "Arial";
// sheet.Range["C13"].Font.Bold = true;
// sheet.Range["D13"].Value = "SUBTOTAL";
// sheet.Range["D13"].Font.Size = 8;
// sheet.Range["D13"].Font.Name = "Arial";
// sheet.Range["D13"].Font.Bold = true;
// lastRow = 13;
// //create category
// var range = sheet.Range["A14", "D14"];
// System.Drawing.Color categoryInteriorColor = System.Drawing.Color.FromArgb(217,217,217);
// range.Merge(0);
// range.Interior.Color = System.Drawing.ColorTranslator.ToOle(categoryInteriorColor);
// range.Borders.Color = System.Drawing.ColorTranslator.ToOle(borderColor);
// range.Value = "******Category Name******";
// range.Font.Size = 10;
// range.Font.Name = "Arial";
// range.Font.Bold = true;
// //create product
// range = sheet.Range["A15", "D15"];
// range.Value = "******Product Name******";
// range.Font.Size = 10;
// range.Font.Name = "Arial";
// range = sheet.Range["A15", "D15"];
// int startRow = range.Row;
// long endRow = long.Parse(range.EntireRow.Rows.CountLarge.ToString());
// int startColumn = range.Column;
// int endColumn = int.Parse(range.EntireColumn.Columns.CountLarge.ToString());
// Console.WriteLine("column:" + startColumn + " entircolumn:" + endColumn);
// Console.WriteLine("row:" + startRow + " endRow:" + endRow);
// //create Description
// range = sheet.Range["A16", "D16"];
// range.Cells.Merge(true);
// range.Value = "******Description******";
// range.Font.Size = 10;
// range.Font.Name = "Arial";
// range.EntireRow.RowHeight = 60;
// //create subtotal
// range = sheet.Range["A17", "C17"];
// range.Cells.Merge(true);
// range.Interior.Color = categoryInteriorColor;
// range.Borders.Color = borderColor;
// range.Value = "Subtotal";
// range.Font.Bold = true;
// range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
// range = sheet.Range["D17"];
// range.Borders.Color = borderColor;
// range.Value = "0.00";
// //create total
// range = sheet.Range["A18", "C18"];
// range.Cells.Merge(true);
// range.Interior.Color = categoryInteriorColor;
// range.Borders.Color = borderColor;
// range.Value = "TOTAL";
// range.Font.Bold = true;
// range.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
// range = sheet.Range["D18"];
// range.Borders.Color = borderColor;
// range.Value = "0.00";
// lastRow = 18;
//}
static void createBottom(Application app)
{
lastRow++;
lastRow++; lastRow++; lastRow++;
var sheet = app.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
var range = sheet.Range["A" + lastRow];
range.Cells.Merge(true);
range.Value = "If you have any questions concerning this quotation, please feel free to contact us.";
lastRow++; lastRow++;
range = sheet.Range["A" + lastRow,"D" + lastRow];
int width = Convert.ToInt32(range.EntireColumn.Width);
Shape textBox = sheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, Convert.ToInt32(range.Left), Convert.ToInt32(range.Top), width, 16);
textBox.TextFrame.Characters(Type.Missing, Type.Missing).Text = "THANK YOU FOR YOUR BUSINESS!";
textBox.TextFrame.Characters(Type.Missing, Type.Missing).Font.Bold = true;
textBox.TextFrame.Characters(Type.Missing, Type.Missing).Font.Size = 10;
textBox.TextFrame.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Microsoft.Office.Core.MsoTextEffectAlignment.msoTextEffectAlignmentCentered;
textBox.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
}
static void demo()
{
string fileName = "c:\\a.xlsx";
var application = new Application();
application.Visible = true;
var workbook = application.Workbooks.Open(fileName);
var worksheet = workbook.Worksheets[2] as Microsoft.Office.Interop.Excel.Worksheet;
//var worksheet = workbook.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
worksheet.Cells[1, 1] = "Hello World!";
//Console.Read();
worksheet.Range["A1"].Value = "Hello Range";
worksheet.Range["A1:c3"].MergeCells = true;
Range range = worksheet.Range["A1:A3"];
range.Name = "range1";
worksheet.Names.Add("range1", range);
//worksheet.Range["range1"].Value;
//worksheet.Range["t1!range1"].Value
//enum range
foreach (Microsoft.Office.Interop.Excel.Name name in worksheet.Names)
{
Console.WriteLine(name.RefersToRange.Cells.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing));
Console.WriteLine(name.RefersToRange.Cells.Worksheet.Name);
Console.WriteLine(name.Name);
}
//search
//set the cell color to red,which cell has the mached value
Range currentFind = null;
Range firstFind = null;
Range Fruits = worksheet.get_Range("A1", "J50");
currentFind = Fruits.Find("9300", Type.Missing,
XlFindLookIn.xlValues, XlLookAt.xlPart,
XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
Type.Missing, Type.Missing);
while (currentFind != null)
{
// Keep track of the first range you find.
if (firstFind == null)
{
firstFind = currentFind;
}
// If you didn't move to a new range, you are done.
else if (currentFind.get_Address(XlReferenceStyle.xlA1)
== firstFind.get_Address(XlReferenceStyle.xlA1))
{
break;
}
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
currentFind = Fruits.FindNext(currentFind);
}
//end search
Console.ReadLine();
application.Quit();
}
}
}
