public Workbook Generate(ShopList shopList)
{
this._shopList = shopList;
Workbook book = new Workbook();
// -----------------------------------------------
// Properties
// -----------------------------------------------
book.Properties.Author = "";
book.Properties.LastAuthor = "";
book.Properties.Created = new System.DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second, 0);
book.Properties.Version = "12.00";
book.ExcelWorkbook.WindowHeight = 10035;
book.ExcelWorkbook.WindowWidth = 23955;
book.ExcelWorkbook.WindowTopX = 0;
book.ExcelWorkbook.WindowTopY = 90;
book.ExcelWorkbook.ProtectWindows = false;
book.ExcelWorkbook.ProtectStructure = false;
// -----------------------------------------------
// Generate Styles
// -----------------------------------------------
this.GenerateStyles(book.Styles);
// -----------------------------------------------
// Generate Sheet1 Worksheet
// -----------------------------------------------
this.GenerateShopListWorksheet(book.Worksheets);
return book;
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open the existing file.
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Get the designer chart in the first worksheet.
Aspose.Cells.Charts.Chart chart = workbook.Worksheets[0].Charts[0];
// Add the third data series to it.
chart.NSeries.Add("{60, 80, 10}", true);
// Add another data series (fourth) to it.
chart.NSeries.Add("{0.3, 0.7, 1.2}", true);
// Plot the fourth data series on the second axis.
chart.NSeries[3].PlotOnSecondAxis = true;
// Change the Border color of the second data series.
chart.NSeries[1].Border.Color = Color.Green;
// Change the Border color of the third data series.
chart.NSeries[2].Border.Color = Color.Red;
// Make the second value axis visible.
chart.SecondValueAxis.IsVisible = true;
// Save the excel file.
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public void Fills()
{
var whiteFill = CellFill.Solid(new Color("#fff")); //white fill
var blackFill = new CellFill { Pattern = FillPattern.Solid, Foreground = Color.Black }; //make sure you set the foreground color
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Chessboard");
var cells = sheet[1, 1, 8, 8];
cells.SetOutsideBorder(new BorderEdge { Color = Color.Black, Style = BorderStyle.Medium });
for (var i = 0; i < cells.Range.Width; i++)
for (var j = 0; j < cells.Range.Height; j++)
cells[j, i].Style.Fill = (i + j) % 2 == 0 ? whiteFill : blackFill;
var rows = cells.GetColumn(-1);
for (var i = 0; i < rows.Range.Height; i++)
{
rows[i].Value = 8 - i;
rows[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
rows[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
var columns = cells.GetRow(8);
for (var i = 0; i < columns.Range.Width; i++)
{
columns[i].Value = new string(new char[] { (Char)('A' + i) });
columns[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
columns[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
sheet.DefaultRowHeight = 40; //px
wb.Save("Fills.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Merging the first three columns in the first row to create a single cell
worksheet.Cells.Merge(0, 0, 1, 3);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open a template Excel file
Workbook workbook = new Workbook(dataDir+ "book1.xlsx");
// Get the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
// Get the A1 cell
Cell cell = sheet.Cells["A1"];
// Get the conditional formatting result object
ConditionalFormattingResult cfr = cell.GetConditionalFormattingResult();
// Get the icon set
ConditionalFormattingIcon icon = cfr.ConditionalFormattingIcon;
// Create the image file based on the icon's image data
File.WriteAllBytes(dataDir+ "imgIcon.out.jpg", icon.ImageData);
// ExEnd:1
}
public static void Run()
{
try
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Unprotecting the worksheet with a password
worksheet.Unprotect("");
// Save Workbook
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
public void Borders()
{
var border = new CellBorder();
border.Left = new BorderEdge { Style = BorderStyle.Double, Color = Color.Black }; //apply double black border to left edge
border.All = new BorderEdge { Style = BorderStyle.Medium, Color = Color.Black }; //apply medium black border to left, right, top and bottom edge
border.Diagonal = new BorderEdge { Style = BorderStyle.Dashed, Color = Color.Black }; // diagonal border is supported also
border.DiagonalUp = true; //in this case you should set which diagonal to use
border.DiagonalDown = false; //in this case you should set which diagonal to use
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Demo Sheet");
sheet["A1"].Style.Border = border; //apply prepared border to cell A1
sheet["B1"].Style.Border = border; //apply prepared border to cell B1
sheet["C1"].Style.Border = border; //apply prepared border to cell C1
sheet["C1"].Style.Border.Bottom = null; //watch out, you have removed bottom border on A1, A2 and A3
var range = sheet["A3", "E10"];
range.SetBorder(new BorderEdge { Style = BorderStyle.Thin, Color = Color.Black }); //border is easier to set on ranges
range.GetRow(0).SetOutsideBorder(new BorderEdge { Style = BorderStyle.Medium, Color = Color.Black }); //header may need a heavier border
range.SetOutsideBorder(new BorderEdge { Style = BorderStyle.Double, Color = Color.Black }); //use double border outside the table
wb.Save("Borders.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Book.xlsx");
// Access the first worksheet of the workbook
Worksheet worksheet = workbook.Worksheets[0];
// Access the first pivot table of the worksheet
PivotTable pivotTable = worksheet.PivotTables[0];
// Apply Average consolidation function to first data field
pivotTable.DataFields[0].Function = ConsolidationFunction.Average;
// Apply DistinctCount consolidation function to second data field
pivotTable.DataFields[1].Function = ConsolidationFunction.DistinctCount;
// Calculate the data to make changes affect
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
public void CreateStaticReport()
{
//Open template
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\designer\book1.xls";
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.PageSetup.SetFooter(0, "&T");
worksheet.PageSetup.SetHeader(1, "&D");
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "HeadersAndFooters.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "HeadersAndFooters.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Hiding the 3rd row of the worksheet
worksheet.Cells.HideRow(2);
// Hiding the 2nd column of the worksheet
worksheet.Cells.HideColumn(1);
// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");
// Closing the file stream to free all resources
fstream.Close();
// ExEnd:1
}
public PipeComponentImporter(Workbook workbook, Dictionary<Int32, List<CellValue>> pipeComponentCellValuesToCheck,
string tagNumberCellName, string lineNumberCellName, string pandIDCellName)
{
mTagNumberCellName = tagNumberCellName;
mLineNumberCellName = lineNumberCellName;
mPandIDCellName = pandIDCellName;
mCee = new CmsEquipmentEntities();
mWorkbook = workbook;
mPipeComponentCellValuesToCheck = pipeComponentCellValuesToCheck;
mExistingPipes = mCee.Pipes
.Include("Area")
.Include("PipeClass")
.Include("PipeSize")
.Include("PipeFluidCode")
.Include("PipeCategory")
.Include("PipeSpecialFeature").ToList();
mImportResult = mCee.PipeComponents
.Include("Pipe")
.Include("Pipe.Area")
.Include("PipeComponentType").ToList();
mDocumentTypes = mCee.DocumentTypes.ToList();
mExistingPipeComponentTypes = mCee.PipeComponentTypes.ToList();
mExistingPipeComponentProperties = mCee.PipeComponentProperties.ToList();
mDrawings = (from x in mDocumentTypes where x.Name == "Drawing" select x.Documents).FirstOrDefault().ToList();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "Template.xlsx";
//Create a workbook object from the template file
Workbook book = new Workbook(filePath);
//Convert each worksheet into svg format in a single page.
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.SaveFormat = SaveFormat.SVG;
imgOptions.OnePagePerSheet = true;
//Convert each worksheet into svg format
foreach (Worksheet sheet in book.Worksheets)
{
SheetRender sr = new SheetRender(sheet, imgOptions);
for (int i = 0; i < sr.PageCount; i++)
{
//Output the worksheet into Svg image format
sr.ToImage(i, filePath + sheet.Name + i + ".out.svg");
}
}
}
private void BTN_Replace_Click(object sender, EventArgs e)
{
if (TXBX_Find.Text != "" && TXBX_Replace.Text!="")
{
workbook = new Workbook(FOD_OpenFile.FileName);
FindOptions Opts = new FindOptions();
Opts.LookInType = LookInType.Values;
Opts.LookAtType = LookAtType.Contains;
string found = "";
Cell cell = null;
foreach (Worksheet sheet in workbook.Worksheets)
{
do
{
cell = sheet.Cells.Find(TXBX_Find.Text, cell, Opts);
if (cell != null)
{
string celltext = cell.Value.ToString();
celltext = celltext.Replace(TXBX_Find.Text, TXBX_Replace.Text);
cell.PutValue(celltext);
}
}
while (cell != null);
}
LBL_FindResults.Text = "Replaced All Existing Values, Save the file now";
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Finding the cell containing the specified formula
Cell cell = worksheet.Cells.FindFormula("=SUM(A5:A10)", null);
// Printing the name of the cell found after searching worksheet
System.Console.WriteLine("Name of the cell containing formula: " + cell.Name);
// Closing the file stream to free all resources
fstream.Close();
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source Excel file
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the Cells collection in the first worksheet
Cells cells = worksheet.Cells;
// Create a cellarea i.e.., A2:B11
CellArea ca = CellArea.CreateCellArea("A2", "B11");
// Apply subtotal, the consolidation function is Sum and it will applied to Second column (B) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, true);
// Set the direction of outline summary
worksheet.Outline.SummaryRowBelow = true;
// Save the excel file
workbook.Save(dataDir + "output_out.xlsx");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Workbook object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Putting a value to the "A1" cell
worksheet.Cells["A1"].PutValue("Visit Aspose");
// Setting the font color of the cell to Blue
worksheet.Cells["A1"].GetStyle().Font.Color = Color.Blue;
// Setting the font of the cell to Single Underline
worksheet.Cells["A1"].GetStyle().Font.Underline = FontUnderlineType.Single;
// Adding a hyperlink to a URL at "A1" cell
worksheet.Hyperlinks.Add("A1", 1, 1, "http:// Www.aspose.com");
// Saving the Excel file
workbook.Save(dataDir + "book1.xls");
// ExEnd:1
}
/// <summary>
/// Converts binary to blob pointer
/// </summary>
/// <param name="category">The file category.</param>
/// <param name="data">The data.</param>
/// <param name="name">The name.</param>
/// <returns>Blob Pointer</returns>
public static string ConvertToFile(string category, string name, byte[] data)
{
if (data == null)
{
return null;
}
using (Stream stream = new MemoryStream(data))
{
// convert doc, xls -> docx, xlsx
Stream streamToSave = new MemoryStream();
if (FileTypeDetector.IsDoc(data))
{
Document doc = new Document(stream);
doc.RemoveMacros();
doc.Save(streamToSave, Aspose.Words.SaveFormat.Docx);
}
else if (FileTypeDetector.IsXls(data))
{
Workbook xls = new Workbook(stream);
xls.RemoveMacro();
xls.Save(streamToSave, Aspose.Cells.SaveFormat.Xlsx);
}
else
{
streamToSave = stream;
}
// save to file
streamToSave.Position = 0;
string result = BlobStoreProvider.Instance.PutBlob(category, name, streamToSave);
return result;
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Setting the width of all columns in the worksheet to 20.5
worksheet.Cells.StandardWidth = 20.5;
//Saving the modified Excel file
workbook.Save(dataDir + "output.xls");
//Closing the file stream to free all resources
fstream.Close();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate the workbook to open the file that contains a chart
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[1];
//Get the first chart in the sheet
Chart chart = worksheet.Charts[0];
//Specify the FilFormat's type to Solid Fill of the first series
chart.NSeries[0].Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
//Get the CellsColor of SolidFill
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
//Create a theme in Accent style
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
//Apply the them to the series
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
//Save the Excel file
workbook.Save(dataDir + "output.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
try
{
// Create a License object
License license = new License();
// Set the license of Aspose.Cells to avoid the evaluation limitations
license.SetLicense(dataDir + "Aspose.Cells.lic");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Instantiate a Workbook object that represents Excel file.
Workbook wb = new Workbook();
// When you create a new workbook, a default "Sheet1" is added to the workbook.
Worksheet sheet = wb.Worksheets[0];
// Access the "A1" cell in the sheet.
Cell cell = sheet.Cells["A1"];
// Input the "Hello World!" text into the "A1" cell
cell.PutValue("Hello World!");
// Save the Excel file.
wb.Save(dataDir + "MyBook_out.xlsx", SaveFormat.Excel97To2003);
// ExEnd:1
}
public void CreateStaticReport()
{
//initialize the workbook object
Workbook workbook = new Workbook();
//Get first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
//Apply WordArt Style with font settings
sheet.Shapes.AddTextEffect(Aspose.Cells.Drawing.MsoPresetTextEffect.TextEffect1, "Aspose.Cells for .NET", "Arial", 15, true, true, 5, 5, 2, 2, 100, 175);
//Apply WordArt Style with font settings
sheet.Shapes.AddTextEffect(Aspose.Cells.Drawing.MsoPresetTextEffect.TextEffect2, "Aspose.Cells for Java", "Verdana", 30, true, false, 10, 5, 2, 2, 100, 100);
//Apply WordArt Style with font settings
sheet.Shapes.AddTextEffect(Aspose.Cells.Drawing.MsoPresetTextEffect.TextEffect3, "Aspose.Cells for Reporting Services", "Times New Roman", 25, false, true, 15, 5, 2, 2, 100, 150);
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "AddingWordArt.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "AddingWordArt.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Creating a Workbook object
Workbook workbook = new Workbook();
//Your Code goes here for any workbook related operations
//Save in Excel 97 – 2003 format
workbook.Save(dataDir + "book1.xls");
//OR
workbook.Save(dataDir + "book1.xls", new XlsSaveOptions(SaveFormat.Excel97To2003));
//Save in Excel2007 xlsx format
workbook.Save(dataDir + "book1.xlsx", SaveFormat.Xlsx);
//Save in Excel2007 xlsb format
workbook.Save(dataDir + "book1.xlsb", SaveFormat.Xlsb);
//Save in ODS format
workbook.Save(dataDir + "book1.ods", SaveFormat.ODS);
//Save in Pdf format
workbook.Save(dataDir + "book1.pdf", SaveFormat.Pdf);
//Save in Html format
workbook.Save(dataDir + "book1.html", SaveFormat.Html);
//Save in SpreadsheetML format
workbook.Save(dataDir + "book1.xml", SaveFormat.SpreadsheetML);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Instantiate a new Workbook
Workbook workbook = new Workbook();
//Get the first worksheet's cells collection
Cells cells = workbook.Worksheets[0].Cells;
//Add string values to the cells
cells["A1"].PutValue("A1");
cells["C10"].PutValue("C10");
//Add a blank picture to the D1 cell
Picture pic = workbook.Worksheets[0].Shapes.AddPicture(0, 3, 10, 6, null);
//Specify the formula that refers to the source range of cells
//pic.Formula = "A1:C10";
//Update the shapes selected value in the worksheet
workbook.Worksheets[0].Shapes.UpdateSelectedValue();
//Save the Excel file.
workbook.Save(dataDir + "output.out.xls");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Hello");
//Setting the font Superscript
Style style = cell.GetStyle();
style.Font.IsSuperscript = true;
cell.SetStyle(style);
//Saving the Excel file
workbook.Save(dataDir+ "Superscript.xls", SaveFormat.Auto);
}
static void Main(string[] args)
{
string MyDir = @"Files\";
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
//Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
//Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
//Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(MyDir + "Adding Formula.xls");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string output1Path = dataDir + "Output.xlsx";
string output2Path = dataDir + "Output.out.ods";
Workbook workbook = new Workbook();
Style style = workbook.CreateBuiltinStyle(BuiltinStyleType.Title);
Cell cell = workbook.Worksheets[0].Cells["A1"];
cell.PutValue("Aspose");
cell.SetStyle(style);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumn(0);
worksheet.AutoFitRow(0);
workbook.Save(output1Path);
Console.WriteLine("File saved {0}", output1Path);
workbook.Save(output2Path);
Console.WriteLine("File saved {0}", output1Path);
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Adding a new worksheet to the Workbook object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Setting the name of the newly added worksheet
worksheet.Name = "My Worksheet";
//Saving the Excel file
workbook.Save(dataDir + "output.xls");
//Closing the file stream to free all resources
fstream.Close();
}
public static void Run()
{
// ExStart:DeterminingLicenseLoading
// The path to the License File
string licPath = @"Aspose.Cells.lic";
// Create workbook object before setting a license
Workbook workbook = new Workbook();
// Check if the license is loaded or not. It will return false
Console.WriteLine(workbook.IsLicensed);
try
{
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Check if the license is loaded or not. Now it will return true
Console.WriteLine(workbook.IsLicensed);
// ExEnd:DeterminingLicenseLoading
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Setting the text direction from right to left
style.TextDirection = TextDirectionType.RightToLeft;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
//Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
//Shrinking the text to fit according to the dimensions of the cell
style.ShrinkToFit = true;
cell.SetStyle(style);
//Saving the Excel file
workbook.Save(dataDir + "book1.xls", SaveFormat.Excel97To2003);
}
/// <summary>
/// Helper method for reading workbooks with a disabled visibility.
/// </summary>
internal void CloseHidden()
{
wb.Close();
wb = null;
}
private void BgExportToExcel_DoWork(object sender, DoWorkEventArgs e)
{
try
{
//GENERAR EXCEL
//OBEJTO QUE PERMITE GESTIONAR DOCUMENTO EXCEL
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
//MOSTRAR ARCHIVO MIENTRAS SE UTILIZA
xlApp.Visible = false;
//DIRECTORIO DONDE SE COPIARÁ EL ARCHIVO TEMPORALMENTE
//PARA EDITARLO
string sru = Util.Global.DIRECTORIO_FORMATOS;
//NOMBRE DEL ARCHIVO TEMPORAL
path = @"" + sru + "\\EXPORT.xlsx";
//COPIAR EL ARCHIVO DE LOS RECURSOS DE LA APLICACIÓN
// A LA RUTA ESTABLECIDA
File.WriteAllBytes(path, Properties.Resources.Excel_export);
//VERIFICAR SI EL ARCHIVO ESTÁ BLOQUEADO O ESTA SIENDO UTILIZADO POR OTRO PROCESO
if (IsFileLocked(new FileInfo(path)))
{
this.Dispatcher.BeginInvoke((System.Action) delegate()
{
System.Windows.MessageBox.Show("El archivo se está ejecutando, debe cerrarlo para continuar",
"Exportar en Excel", MessageBoxButton.OK, MessageBoxImage.Information);
});
}
else
{
//CREAR EL OBJETO DEL LIBRO A PARTIR DE LA RUTA
Workbook wb = xlApp.Workbooks.Open(path);
//FIJAR EN OBJETOS LAS PESTAÑAS U HOJA DEL LIBRO PARA TRABAJAR
Worksheet ws = (Worksheet)wb.Worksheets["Hoja1"];
//PROCESO PARA CONVERTIR UNA LISTA GENERICA DE OBJETOS EN DATATABLE PARA
//FACILITAR SU PROCESO DE ESCRITURA EN EL ARCHIVO EXCEL
System.Data.DataTable dt = ConvertToDataTable <MaxAndMin>(listToExport);
//CICLOS PARA RECORRER FILAS Y COLUMNAS Y ESCRIBIR EN EL ARCHIVO EXCEL
for (int i = 2; i <= dt.Rows.Count + 1; i++)
{
for (int j = 1; j < dt.Columns.Count + 1; j++)
{
if (j == 7)
{
if (dt.Rows[i - 2][j - 1].ToString().Equals("0"))
{
//LINEA
FillCell(ws, i, j, "Todas");
}
if (dt.Rows[i - 2][j - 1].ToString().Equals("1"))
{
//LINEA
FillCell(ws, i, j, "Jabones");
}
if (dt.Rows[i - 2][j - 1].ToString().Equals("2"))
{
//LINEA
FillCell(ws, i, j, "Detergentes");
}
}
else
{
//LINEA
FillCell(ws, i, j, dt.Rows[i - 2][j - 1].ToString());
}
}
}
//GUARDAR Y CERRAR CONEXIÓN CON ARCHIVO EXCEL
wb.Save();
wb.Close();
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.ToString());
}
}
public static OpenOrdersInfo ReadOpenOrders(string ticker)
{
dbWriter.InsertGeneral("ExcelReader.ReadOpenOrders: Started", ticker);
Application xlApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Workbook xlWorkbook = xlApp.Workbooks[ExcelPositionFileName];
Worksheet xlWorksheet = (Worksheet)xlWorkbook.Sheets[ExcelOrdersSheetName];
Range xlRange = xlWorksheet.UsedRange;
Range xlCells = xlRange.Cells;
string buyOrderId = null;
double buyPrice = 0;
double buyStopPrice = 0;
double buyVolume = 0;
double buyFilledVolume = 0;
string sellOrderId = null;
double sellPrice = 0;
double sellStopPrice = 0;
double sellVolume = 0;
double sellFilledVolume = 0;
var ordersInfoRow = 1;
int buyOrders = 0;
int sellOrders = 0;
List <string> buyOrdersIds = new List <string>();
List <double> buyPrices = new List <double>();
List <double> buyStopPrices = new List <double>();
List <double> buyVolumes = new List <double>();
List <double> buyFilledVolumes = new List <double>();
List <string> sellOrdersIds = new List <string>();
List <double> sellPrices = new List <double>();
List <double> sellStopPrices = new List <double>();
List <double> sellVolumes = new List <double>();
List <double> sellFilledVolumes = new List <double>();
while ((xlCells[ordersInfoRow, OrderTickerColumn] as Range).Value2 != null)
{
if ((xlCells[ordersInfoRow, OrderTickerColumn] as Range).Value2.ToString().Equals(ticker))
{
string status = (xlCells[ordersInfoRow, StatusColumn] as Range).Value2.ToString();
if (status.Equals("Open") || status.Equals("Partial") || status.Equals("Pending"))
{
string side = (xlCells[ordersInfoRow, OrderSideColumn] as Range).Value2.ToString();
if (side.Equals("Buy"))
{
buyOrders++;
buyOrderId = (xlCells[ordersInfoRow, OrderIdColumn] as Range).Value2.ToString().Substring(2);
buyOrdersIds.Add(buyOrderId);
buyPrice = (double)(xlCells[ordersInfoRow, PriceColumn] as Range).Value2;
buyPrices.Add(buyPrice);
buyStopPrice = (double)(xlCells[ordersInfoRow, StopPriceColumn] as Range).Value2;
buyStopPrices.Add(buyStopPrice);
buyVolume = (double)(xlCells[ordersInfoRow, BalanceColumn] as Range).Value2;
buyVolumes.Add(buyVolume);
buyFilledVolume = (double)(xlCells[ordersInfoRow, VolumeColumn] as Range).Value2 - buyVolume;
buyFilledVolumes.Add(buyFilledVolume);
}
else
{
sellOrders++;
sellOrderId = (xlCells[ordersInfoRow, OrderIdColumn] as Range).Value2.ToString().Substring(2);
sellOrdersIds.Add(sellOrderId);
sellPrice = (double)(xlCells[ordersInfoRow, PriceColumn] as Range).Value2;
sellPrices.Add(sellPrice);
sellStopPrice = (double)(xlCells[ordersInfoRow, StopPriceColumn] as Range).Value2;
sellStopPrices.Add(sellStopPrice);
sellVolume = (double)(xlCells[ordersInfoRow, BalanceColumn] as Range).Value2;
sellVolumes.Add(sellVolume);
sellFilledVolume = (double)(xlCells[ordersInfoRow, VolumeColumn] as Range).Value2 - sellVolume;
sellFilledVolumes.Add(sellFilledVolume);
}
}
}
ordersInfoRow++;
}
dbWriter.InsertGeneral("ExcelReader.OpenOrdersInfo: Finished", ticker);
return(new OpenOrdersInfo(buyOrdersIds, buyPrices, buyStopPrices, buyVolumes, buyFilledVolumes
, sellOrdersIds, sellPrices, sellStopPrices, sellVolumes, sellFilledVolumes, ticker));
}
private void ImportProduct(string path)
{
string message = "";
string improtmessage = "";
AC_AccountMonth month = new AC_AccountMonthBLL(AC_AccountMonthBLL.GetCurrentMonth() - 1).Model;
DateTime minday = month.BeginDate;
DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
int _State = 0;
#region 读取Excel文件
string ErrorInfo = "";
object missing = System.Reflection.Missing.Value;
ApplicationClass ExcelApp = null;
try
{
ExcelApp = new ApplicationClass();
ExcelApp.Visible = false;
ExcelApp.DisplayAlerts = false;
Workbook workbook1 = null;
Worksheet worksheet1 = null;
try
{
workbook1 = ExcelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
#region 验证工作表数据格式
worksheet1 = (Worksheet)workbook1.Worksheets[1];
if (worksheet1.Name != "零售商销货")
{
MessageBox.Show("Excel表格中第1个工作表名称必须为【零售商销货】！");
goto End;
}
if (!VerifyWorkSheet(worksheet1, 7))
{
MessageBox.Show("零售商销货工作表表头(1~7列)错误!");
goto End;
}
IList <PDT_Product> productlists = PDT_ProductBLL.GetModelList("Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code");
for (int i = 0; i < productlists.Count; i++)
{
if (((Range)worksheet1.Cells[1, 8 + i]).Text.ToString() != productlists[i].ShortName)
{
MessageBox.Show("零售商进货工作表表头，(" + (8 + i).ToString() + "列)产品名称错误！必须为:" + productlists[i].ShortName);
goto End;
}
}
#endregion
improtmessage += DoImportProduct(worksheet1, month.ID, 1, 8, productlists, out _State);
End:
;
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "\r\n" + "Source:" + err.Source + "\r\n" +
"StackTrace:" + err.StackTrace + "\r\n";
MessageBox.Show(error);
}
finally
{
if (workbook1 != null)
{
workbook1.Close(false, missing, missing);
}
if (worksheet1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1);
}
if (workbook1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1);
}
worksheet1 = null;
workbook1 = null;
}
}
catch (ThreadAbortException exception3)
{
return;
}
catch (System.Exception err)
{
MessageBox.Show("系统错误-5!" + err.Message);
}
finally
{
if (ExcelApp != null)
{
try
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "\r\n" + "Source:" + err.Source + "\r\n" +
"StackTrace:" + err.StackTrace + "\r\n";
MessageBox.Show(error + "系统错误-6,Excel宏报错，请确认文件打开不报错再上传!" + err.Message);
KillProcess();
}
}
GC.Collect();
//GC.WaitForPendingFinalizers();
if (ErrorInfo != "")
{
MessageBox.Show("对不起，Excel文件打开错误，请确认格式是否正确。错误提示:" + ErrorInfo);
}
}
#endregion
string filename = path.Substring(path.LastIndexOf('\\') + 1);
//MessageBox.Show("导入成品进销", message != "" ? filename + "-" + message : filename + "导入操作成功！");
}
public override void Generate(bool show)
{
#if !NETSTANDARD
fPath = AppHost.StdDialogs.GetSaveFile("Excel files (*.xls)|*.xls");
if (string.IsNullOrEmpty(fPath))
{
return;
}
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
IProgressController progress = AppHost.Progress;
progress.ProgressInit(LangMan.LS(LSID.LSID_MIExport) + "...", fTree.RecordsCount);
var dateFormat = GlobalOptions.Instance.DefDateFormat;
try
{
worksheet.Cells[1, 1] = new Cell("№");
worksheet.Cells[1, 2] = new Cell(LangMan.LS(LSID.LSID_Surname));
worksheet.Cells[1, 3] = new Cell(LangMan.LS(LSID.LSID_Name));
worksheet.Cells[1, 4] = new Cell(LangMan.LS(LSID.LSID_Patronymic));
worksheet.Cells[1, 5] = new Cell(LangMan.LS(LSID.LSID_Sex));
worksheet.Cells[1, 6] = new Cell(LangMan.LS(LSID.LSID_BirthDate));
worksheet.Cells[1, 7] = new Cell(LangMan.LS(LSID.LSID_DeathDate));
worksheet.Cells[1, 8] = new Cell(LangMan.LS(LSID.LSID_BirthPlace));
worksheet.Cells[1, 9] = new Cell(LangMan.LS(LSID.LSID_DeathPlace));
worksheet.Cells[1, 10] = new Cell(LangMan.LS(LSID.LSID_Residence));
worksheet.Cells[1, 11] = new Cell(LangMan.LS(LSID.LSID_Age));
worksheet.Cells[1, 12] = new Cell(LangMan.LS(LSID.LSID_LifeExpectancy));
ushort row = 1;
int num = fTree.RecordsCount;
for (int i = 0; i < num; i++)
{
GDMRecord rec = fTree[i];
if (rec.RecordType == GDMRecordType.rtIndividual)
{
GDMIndividualRecord ind = (GDMIndividualRecord)rec;
if (fSelectedRecords == null || fSelectedRecords.IndexOf(rec) >= 0)
{
var parts = GKUtils.GetNameParts(fTree, ind);
string sx = "" + GKUtils.SexStr(ind.Sex)[0];
row++;
worksheet.Cells[row, 1] = new Cell(ind.GetXRefNum());
worksheet.Cells[row, 2] = new Cell(parts.Surname);
worksheet.Cells[row, 3] = new Cell(parts.Name);
worksheet.Cells[row, 4] = new Cell(parts.Patronymic);
worksheet.Cells[row, 5] = new Cell(sx);
worksheet.Cells[row, 6] = new Cell(GKUtils.GetBirthDate(ind, dateFormat, false));
worksheet.Cells[row, 7] = new Cell(GKUtils.GetDeathDate(ind, dateFormat, false));
worksheet.Cells[row, 8] = new Cell(GKUtils.GetBirthPlace(ind));
worksheet.Cells[row, 9] = new Cell(GKUtils.GetDeathPlace(ind));
worksheet.Cells[row, 10] = new Cell(GKUtils.GetResidencePlace(ind, fOptions.PlacesWithAddress));
worksheet.Cells[row, 11] = new Cell(GKUtils.GetAge(ind, -1));
worksheet.Cells[row, 12] = new Cell(GKUtils.GetLifeExpectancy(ind));
}
}
progress.ProgressStep();
}
workbook.Worksheets.Add(worksheet);
workbook.Save(fPath);
#if !CI_MODE
if (show)
{
ShowResult();
}
#endif
}
finally
{
progress.ProgressDone();
}
#endif
}
public DataTable LerArquivo(string caminhoArquivo)
{
var app = new Application();
//Cria um objeto do tipo WorkBook com todos os elementos do Excel.
Workbook workBook = app.Workbooks.Open(caminhoArquivo,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
int numSheets = workBook.Sheets.Count;
//esse loop vai percorrer todas as pastas de trabalho do excel.
for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
{
Worksheet sheet = (Worksheet)workBook.Sheets[sheetNum];
int numColumns = sheet.Columns.Count;
int numRows = sheet.Rows.Count;
Range excelRange = sheet.UsedRange;
//Pega o conteúdo de uma linha e transforma e um array de objetos.
object[,] Linha = (object[, ])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
List <string[]> Linhas = new List <string[]>();
int cont_ant = 0;
//Percorre todas as dimensões do array linha para pegar o conteúdo de cada célula.
for (int i = 1; i <= Linha.GetUpperBound(0); i++)
{
if (i > cont_ant)
{
cont_ant = i;
//pega o total de células preenchidas na linha e cria um array com o número exato de dimensões.
string[] celulas = new string[Linha.GetUpperBound(1)];
//percorre todas as células atribuindo preenchendo o array.
for (int j = 1; j <= Linha.GetUpperBound(1); j++)
{
if (Linha[i, j] != null)
{
celulas[j - 1] = Linha[i, j].ToString();
}
}
Linhas.Add(celulas);
}
}
var arr = new List <Tuple <string, string, int, int> >();
arr.Add(Tuple.Create("info", "numero", 1, 0));
arr.Add(Tuple.Create("info", "nome", 2, 0));
arr.Add(Tuple.Create("info", "largada", 4, 0));
arr.Add(Tuple.Create("calc", "modalidade", 6, 4));
arr.Add(Tuple.Create("info", "chegada", 17, 0));
arr.Add(Tuple.Create("info", "total", 18, 0));
//while (vLinha < cont_ant)
//Criando as colunas na datatable pra receber os valores que serão calculados
foreach (var vColunas in arr)
{
//table.Columns.Add("largada");
table.Columns.Add(vColunas.Item2);
}
foreach (var tuple in arr)
{
if (tuple.Item1 == "info")
{
if (tuple.Item2 == "numero")
{
for (var vLinha = 1; vLinha <= cont_ant; vLinha++)
{
var vNumero = Linha[vLinha, Convert.ToInt32(tuple.Item3)].ToString();
table.Rows.Add(vNumero);
}
}
else if (tuple.Item2 == "nome")
{
for (var vLinha = 1; vLinha <= (table.Rows.Count); vLinha++)
{
var vNome = Linha[vLinha, Convert.ToInt32(tuple.Item3)].ToString();
table.Rows[vLinha - 1][tuple.Item2] = vNome;
}
}
else if (tuple.Item2 == "largada")
{
for (var vLinha = 1; vLinha <= (table.Rows.Count); vLinha++)
{
var vLargada = ConverterHorario(Linha[vLinha, Convert.ToInt32(tuple.Item3)].ToString());
table.Rows[vLinha - 1][tuple.Item2] = vLargada;
}
}
else if (tuple.Item2 == "chegada")
{
for (var vLinha = 1; vLinha <= (table.Rows.Count); vLinha++)
{
var vChegada = Linha[vLinha, Convert.ToInt32(tuple.Item3)].ToString();
table.Rows[vLinha - 1][tuple.Item2] = vChegada;
}
}
else if (tuple.Item2 == "total")
{
for (var vLinha = 1; vLinha <= (table.Rows.Count); vLinha++)
{
var vTotal = Linha[vLinha, Convert.ToInt32(tuple.Item3)].ToString();
table.Rows[vLinha - 1][tuple.Item2] = vTotal;
}
}
}
else if (tuple.Item1 == "calc")
{
if (tuple.Item2 == "modalidade")
{
for (var vLinha = 1; vLinha <= (table.Rows.Count); vLinha++)
{
var vValor1 = Linha[vLinha, Convert.ToInt32(tuple.Item3)];
var vValor2 = Linha[vLinha, Convert.ToInt32(tuple.Item4)];
var vFinal = ((double)vValor2 - (double)vValor1).ToString();
table.Rows[vLinha - 1][tuple.Item2] = ConverterHorario(vFinal);
}
}
}
}
}
workBook.Close();
return(table);
}
private WorkBook(Workbook wb, string filePath)
{
this.wb = wb;
Name = filePath;
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
List <Tartim> lst = ((DataParameter)e.Argument).liste;
string fileName = ((DataParameter)e.Argument).Filename;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)excel.ActiveSheet;
excel.Visible = false;
int index = 1;
int proccess = lst.Count;
ws.Cells[1, 1] = "Tartım Kod";
ws.Cells[1, 2] = "Arac Plaka";
ws.Cells[1, 3] = "Islem Sirasi";
ws.Cells[1, 4] = "Olcum Tarihi";
ws.Cells[1, 5] = "Alici Firma";
ws.Cells[1, 6] = "Malzeme";
ws.Cells[1, 7] = "Sevk Yeri";
ws.Cells[1, 8] = "Sofor";
ws.Cells[1, 9] = "Net Agirlik";
ws.Cells[1, 10] = "Brüt Agirlik";
ws.Cells[1, 11] = "Dara Agirlik";
ws.Cells[1, 12] = "Ucret";
ws.Cells[1, 13] = "Gonderen Firma";
ws.Cells[1, 14] = "Nakliyeci Firma";
ws.Cells[1, 15] = "Islem Yapan Yetkili";
foreach (var item in lst)
{
if (!backgroundWorker.CancellationPending)
{
backgroundWorker.ReportProgress(index++ *100 / proccess);
ws.Cells[index, 1] = item.TartimId.ToString();
ws.Cells[index, 2] = item.Arac.AracPlaka.ToString();
ws.Cells[index, 3] = item.IslemSirasi.ToString();
ws.Cells[index, 4] = item.OlcumTarihi.ToString();
ws.Cells[index, 5] = item.CariUnvan_Firma.CariUnvan_FirmaAd.ToString();
ws.Cells[index, 6] = item.Malzeme.MalzemeAd.ToString();
ws.Cells[index, 7] = item.SevkYeri.SevkYeriAdres.ToString();
ws.Cells[index, 8] = item.Sofor.SoforAdSoyad.ToString();
ws.Cells[index, 9] = item.NetAgirlik.ToString();
ws.Cells[index, 10] = (item.NetAgirlik + item.Arac.AracDaraAgirlik).ToString();
ws.Cells[index, 11] = item.Arac.AracDaraAgirlik.ToString();
ws.Cells[index, 12] = "TL " + item.Ucret.ToString();
ws.Cells[index, 13] = item.CariUnvan_Firma2.CariUnvan_FirmaAd.ToString();
if (item.CariUnvan_Firma1 != null)
{
ws.Cells[index, 14] = item.CariUnvan_Firma1.CariUnvan_FirmaAd.ToString();
}
else
{
ws.Cells[index, 14] = "Yok";
}
if (item.Yetkili == null)
{
ws.Cells[index, 15] = "Yok";
}
else
{
ws.Cells[index, 15] = item.Yetkili.YetkiliKullaniciAd.ToString();
}
}
}
ws.SaveAs(fileName, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void AttempImprimir()
{
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.DefaultExt = ".xlsx";
dlg.Filter = "Documentos Excel (.xlsx)|*.xlsx";
if (dlg.ShowDialog() == true)
{
string filename = dlg.FileName;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
Workbook excelPrint = excel.Workbooks.Open(@"C:\Programs\ElaraInventario\Resources\TraspasoStock.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1];
//Folio
excel.Cells[8, 6] = _movimientoModel.UnidMovimiento.ToString();
//Fecha
excel.Cells[8, 23] = _movimientoModel.FechaMovimiento;
//Solicitante y su área
excel.Cells[11, 12] = _movimientoModel.SolicitanteLectura.SOLICITANTE_NAME;
excel.Cells[13, 12] = _movimientoModel.DepartamentoLectura.DEPARTAMENTO_NAME;
//Empresa
excel.Cells[15, 12] = _movimientoModel.EmpresaLectura.EMPRESA_NAME;
try
{
//Procedencia
excel.Cells[19, 12] = "Almacén: " + _movimientoModel.AlmacenProcedenciaLectura.ALMACEN_NAME;
//Técnico
excel.Cells[21, 12] = _movimientoModel.Tecnico.TECNICO_NAME;
//Destino
excel.Cells[25, 12] = "Almacén: " + _movimientoModel.AlmacenDestino.ALMACEN_NAME;
//Técnico
excel.Cells[27, 12] = _movimientoModel.TecnicoTrnas.TECNICO_NAME;
}
catch (Exception Ex)
{
}
//TT
excel.Cells[31, 12] = _movimientoModel.Tt;
//Transporte
excel.Cells[35, 12] = _movimientoModel.Transporte.TRANSPORTE_NAME;
//Guia
excel.Cells[37, 12] = _movimientoModel.Guia;
int X = 44;
Microsoft.Office.Interop.Excel.Borders borders;
for (int i = 0; i < ItemModel.ItemModel.Count; i++)
{
//for (int i = 0; i < 5; i++) {
//No.
excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Merge();
excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 2] = (i + 1).ToString() + ".-";
borders = excel.Range[excel.Cells[X, 2], excel.Cells[X, 3]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//DESCRIPCIÓN
excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Merge();
excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 4] = ItemModel.ItemModel[i].Articulo.ARTICULO1;
borders = excel.Range[excel.Cells[X, 4], excel.Cells[X, 22]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//N° DE SERIE
excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Merge();
excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 23] = ItemModel.ItemModel[i].NUMERO_SERIE;
borders = excel.Range[excel.Cells[X, 23], excel.Cells[X, 26]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//SKU
excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Merge();
excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 27] = ItemModel.ItemModel[i].SKU;
borders = excel.Range[excel.Cells[X, 27], excel.Cells[X, 30]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//CANTIDAD
excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Merge();
excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 31] = ItemModel.ItemModel[i].CantidadMovimiento;
borders = excel.Range[excel.Cells[X, 31], excel.Cells[X, 34]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
X++;
}
X += 2;
excel.Cells[X, 3] = "OBSERVACIONES:";
excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Merge();
borders = excel.Range[excel.Cells[X, 9], excel.Cells[X + 2, 33]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
X += 4;
excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].Merge();
excel.Range[excel.Cells[X, 2], excel.Cells[X, 17]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 2] = "ENTREGADO POR:";
excel.Cells[X, 2].Font.Bold = true;
excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].Merge();
excel.Range[excel.Cells[X, 18], excel.Cells[X, 34]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.Cells[X, 18] = "RECIBIDO POR:";
excel.Cells[X, 18].Font.Bold = true;
X += 1;
excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Merge();
borders = excel.Range[excel.Cells[X, 2], excel.Cells[X + 2, 17]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Merge();
borders = excel.Range[excel.Cells[X, 18], excel.Cells[X + 2, 34]].Borders;
borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excelSheetPrint.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Visible = true;
}
}
private int ParseRow(XmlReader rowreader,
Workbook wb,
Sheet sheet,
int row,
IDictionary <string, Cell> cellParsingCache)
{
/* XMLSS has origin at (1,1) = (A,1) whereas Corecalc internal
* representation has origin at (0,0) = (A,1). Hence the col
* and row indices are 1 higher in this method.
*/
int cellCount = 0;
int col = 0;
XmlReader cellreader = rowreader.ReadSubtree();
while (cellreader.ReadToFollowing("Cell"))
{
String colindexstr = cellreader.GetAttribute("ss:Index");
String arrayrangestr = cellreader.GetAttribute("ss:ArrayRange");
String formulastr = cellreader.GetAttribute("ss:Formula");
String typestr = "";
String dataval = "";
if (colindexstr != null)
{
if (!int.TryParse(colindexstr, out col))
{
col = 0; // Looks wrong, should be 1?
}
}
else
{
col++;
}
cellCount++;
// If an array result occupies cells, do not overwrite
// the formula with precomputed and cached data from
// the XMLSS file. Instead skip the parsing and sheet update.
if (sheet[col - 1, row - 1] != null)
{
continue;
}
using (XmlReader datareader = cellreader.ReadSubtree()) {
if (datareader.ReadToFollowing("Data"))
{
typestr = datareader.GetAttribute("ss:Type");
datareader.MoveToContent();
dataval = datareader.ReadElementContentAsString();
}
}
String cellString;
if (formulastr != null)
{
cellString = formulastr;
}
else
{
// Anything else than formulas are values.
// If XMLSS tells us it is a String we believe it
if (typestr == "String")
{
dataval = "'" + dataval;
}
cellString = dataval;
}
// Skip blank cells
if (cellString == "")
{
continue;
}
Cell cell;
if (cellParsingCache.TryGetValue(cellString, out cell))
{
// Copy the cell (both for mutable Formula cells and for cell-specific
// metadata) and but share any sharable contents.
cell = cell.CloneCell(col - 1, row - 1);
}
else
{
// Cell contents not seen before: scan, parse and cache
cell = Cell.Parse(cellString, wb, col, row);
if (cell == null)
{
Console.WriteLine("BAD: Null cell from \"{0}\"", cellString);
}
else
{
cellParsingCache.Add(cellString, cell);
}
}
if (arrayrangestr != null && cell is Formula) // Array formula
{
string[] split = arrayrangestr.Split(":".ToCharArray());
RARef raref1 = new RARef(split[0]);
RARef raref2;
if (split.Length == 1)
{
// FIXME: single cell result, but still array
raref2 = new RARef(split[0]);
}
else
{
raref2 = new RARef(split[1]);
}
CellAddr ulCa = raref1.Addr(col - 1, row - 1);
CellAddr lrCa = raref2.Addr(col - 1, row - 1);
// This also updates support sets, but that's useless, because
// they will subsequently be reset by RebuildSupportGraph
sheet.SetArrayFormula(cell, col - 1, row - 1, ulCa, lrCa);
}
else // One-cell formula, or constant
{
sheet[col - 1, row - 1] = cell;
}
}
cellreader.Close();
return(cellCount);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="objects"></param>
/// <param name="sheetName">generated sheet name, Do not set empty value for this parameters</param>
/// <param name="headerNames"></param>
/// <returns></returns>
public Stream Do <T>(List <T> objects, string sheetName, ExcelHeaderList headerNames)
{
var stream = new MemoryStream();
sheetName = string.IsNullOrEmpty(sheetName) ? "Mayhedi_Sheet" : sheetName;
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(sheetName);
// Create Styles and Insert into Workbook
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var data = objects.Select(o => (object)o).ToList();
if (headerNames == null)
{
headerNames = new ExcelHeaderList();
foreach (var o in ObjUtility.GetPropertyInfo(objects[0]))
{
headerNames.Add(o, o);
}
}
var sheetData = CreateSheetData(data, headerNames, stylesPart);
var worksheet = new Worksheet();
var numCols = headerNames.Count;
var width = 20;//headerNames.Max(h => h.Length) + 5;
var columns = new Columns();
for (var col = 0; col < numCols; col++)
{
var c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (c != null)
{
columns.Append(c);
}
}
worksheet.Append(columns);
var sheets = new Sheets();
var sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
document.Close();
}
return(stream);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Create a workbook.
Workbook workbook = new Workbook();
// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Put a string value into the A1 cell.
cells["A1"].PutValue("Please enter Date b/w 1/1/1970 and 12/31/1999");
// Set row height and column width for the cells.
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);
// Get the validations collection.
ValidationCollection validations = workbook.Worksheets[0].Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Add a new validation.
Validation validation = validations[validations.Add(ca)];
// Set the data validation type.
validation.Type = ValidationType.Date;
// Set the operator for the data validation
validation.Operator = OperatorType.Between;
// Set the value or expression associated with the data validation.
validation.Formula1 = "1/1/1970";
// The value or expression associated with the second part of the data validation.
validation.Formula2 = "12/31/1999";
// Enable the error.
validation.ShowError = true;
// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the title of the data-validation error dialog box
validation.ErrorTitle = "Date Error";
// Set the data validation error message.
validation.ErrorMessage = "Enter a Valid Date";
// Set and enable the data validation input message.
validation.InputMessage = "Date Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;
// Set a collection of CellArea which contains the data validation settings.
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
// Add the validation area.
validation.AddArea(cellArea);
// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
private void btnBrowseExcel_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
if (xlsApp == null)
{
MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
}
else
{
Workbook wb = xlsApp.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
Sheets sheets = wb.Worksheets;
Worksheet ws = (Worksheet)sheets.get_Item(1);
Range firstColumn = ws.UsedRange.Columns[1];
System.Array myvalues = (System.Array)firstColumn.Cells.Value;
string[] excelNumbers = myvalues.OfType <object>().Select(o => o.ToString()).ToArray();
if (!String.IsNullOrEmpty(txtTo.Text))
{
for (int i = 0; i < excelNumbers.Length; i++)
{
if (excelNumbers[i][0] == '0')
{
txtTo.Text += "," + excelNumbers[i];
}
else
{
txtTo.Text += ",0" + excelNumbers[i];
}
}
}
else
{
for (int i = 0; i < excelNumbers.Length; i++)
{
if (excelNumbers[i][0] == '0')
{
txtTo.Text += excelNumbers[i] + ",";
}
else
{
txtTo.Text += "0" + excelNumbers[i] + ",";
}
}
txtTo.Text = txtTo.Text.Remove(txtTo.TextLength - 1);
}
MessageBox.Show("Numbers imported successfully !");
}
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
/**/
/// <summary>
/// 将Word文档转换为图片的方法
/// </summary>
/// <param name="wordInputPath">Word文件路径</param>
/// <param name="imageOutputDirPath">图片输出路径，如果为空，默认值为Word所在路径</param>
/// <param name="startPageNum">从PDF文档的第几页开始转换，如果为0，默认值为1</param>
/// <param name="endPageNum">从PDF文档的第几页开始停止转换，如果为0，默认值为Word总页数</param>
/// <param name="resolution">设置图片的像素，数字越大越清晰，如果为0，默认值为128，建议最大值不要超过1024</param>
private void _ConvertToImage(string filepath, string outpath, int startPage, int endPage, int resolution = 300)
{
try
{
var workBook = new Workbook(filepath);
if (workBook == null)
{
throw new Exception("Excel文件无效或者Excel文件已被加密！");
}
var iop = new ImageOrPrintOptions();
iop.ImageFormat = ImageFormat.Png;
//iop.AllColumnsInOnePagePerSheet = true;
iop.ChartImageType = ImageFormat.Png;
iop.OnePagePerSheet = true;
iop.HorizontalResolution = 400;
iop.VerticalResolution = 400;
if (!Directory.Exists(outpath))
{
Directory.CreateDirectory(outpath);
}
if (startPage <= 0)
{
startPage = 0;
}
if (endPage > workBook.Worksheets.Count || endPage <= 0)
{
endPage = workBook.Worksheets.Count;
}
if (resolution <= 0)
{
resolution = 300;
}
for (int index = startPage; index < endPage; index++)
{
if (this._cancelled)
{
break;
}
Worksheet item = workBook.Worksheets[index];
SheetRender sr = new SheetRender(item, iop);
for (int kindex = 0; kindex < sr.PageCount; kindex++)
{
sr.ToImage(kindex, outpath + item.Name + ".png");
System.Threading.Thread.Sleep(200);
}
if (!_cancelled && this.OnProgressChanged != null)
{
this.OnProgressChanged(index + 1, endPage);
}
}
if (this._cancelled)
{
return;
}
if (this.OnConvertSucceed != null)
{
this.OnConvertSucceed();
}
}
catch (Exception ex)
{
if (this.OnConvertFailed != null)
{
this.OnConvertFailed(ex.Message);
}
}
}
private void exportExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
//export to excel
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\FinalData.mdf;Integrated Security=True");
SqlCommand commandor = new SqlCommand("SELECT * FROM Table12", con); //uncleaned
SqlCommand command = new SqlCommand("SELECT * FROM clean", con); //Clean data
SqlCommand comm = new SqlCommand("SELECT * FROM duplicate", con); //Duplicats
SqlCommand com = new SqlCommand("SELECT * FROM InvalidID", con); //invalidID
SqlDataAdapter firstadapter = new SqlDataAdapter(commandor.CommandText, con);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, con);
SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm.CommandText, con);
SqlDataAdapter datareaderinvalidId = new SqlDataAdapter(com.CommandText, con);
string file = open.FileName;
//datatables tobe filled with data from sql
DataTable t = new DataTable();
DataTable table = new DataTable();
DataTable table2 = new DataTable();
DataTable tableinvalid = new DataTable();
dataAdapter.Fill(t);
firstadapter.Fill(table);
dataAdapter1.Fill(table2);
datareaderinvalidId.Fill(tableinvalid);
// workbooks to export excel
Workbook book = new Workbook();
book.CreateEmptySheets(4);
Worksheet sheet1 = book.Worksheets[0];
sheet1.Name = textBox2.Text;
//style sheet
sheet1.Range["A1:N1"].Style.Font.IsBold = true;
sheet1.Range["A1:N1"].Style.Color = Color.Gray;
sheet1.InsertDataTable(table, true, 1, 1);
//------------------------------------------------------------
Worksheet sheet = book.Worksheets[1];
sheet.Name = "clean";
//style sheet
sheet.Range["A1:N1"].Style.Font.IsBold = true;
sheet.Range["A1:N1"].Style.Color = Color.Gray;
sheet.InsertDataTable(t, true, 1, 1);
Worksheet sheet2 = book.Worksheets[2];
sheet2.Name = "Duplicates";
//style sheet
sheet2.Range["A1:N1"].Style.Font.IsBold = true;
sheet2.Range["A1:N1"].Style.Color = Color.Gray;
sheet2.InsertDataTable(table2, true, 1, 1);
Worksheet sheet3 = book.Worksheets[3];
sheet3.Name = "InvalidID";
//style sheet
sheet3.Range["A1:N1"].Style.Font.IsBold = true;
sheet3.Range["A1:N1"].Style.Color = Color.Gray;
sheet3.InsertDataTable(tableinvalid, true, 1, 1);
book.SaveToFile(file, ExcelVersion.Version2010);
System.Diagnostics.Process.Start(file);
MessageBox.Show("successfully exported to excel ", "Export To DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
//string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
}
private void CreateWorkbook()
{
this.workbook = new Workbook();
this.worksheet = workbook.Worksheets.Add();
this.worksheet.Name = "Expense Report 2014";
}
public void CreateNewFile()
{
this.wb = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
this.ws = wb.Worksheets[1];
}
protected override void Build(System.Xml.XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
}
else
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
}
////寫入代號
//ws.Cells[index,6].PutValue("代碼："+source.SelectSingleNode("@學校代號").InnerText+"-"+list.SelectSingleNode("@科別代號").InnerText);
////寫入校名、學年度、學期、科別
//ws.Cells[index+2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
//ws.Cells[index+2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
//ws.Cells[index+2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//複製頁面
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
{
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□畢業", "■畢業"));
}
else
{
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□結業", "■結業"));
}
}
//填入資料
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
{
ssn = rec.SelectSingleNode("@身份證號").InnerText;
}
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" + rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[dataIndex, 0].PutValue("合計");
ws.Cells[dataIndex, 1].PutValue(list.ChildNodes.Count.ToString());
//ws.Cells[index + 22, 0].PutValue("合計");
//ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + 24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region 畢業異動,電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
// 取得日進校
string SchoolType = Util.GetSchoolType1();
foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// 上傳類別
if (SchoolType == "日校")
{
DyWb.Cells[DyWb_index, 2].PutValue("a");
}
else
{
DyWb.Cells[DyWb_index, 2].PutValue("b");
}
//學號
DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
//身分證字號
if (Record.GetAttribute("身分證號") == "")
{
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身份證號"));
}
else
{
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
}
//註1
DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("註1"));
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
//出生日期
if (!string.IsNullOrEmpty(Record.GetAttribute("生日")))
{
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日")));
}
else
{
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日1")));
}
// 特殊身分代碼
DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身分代碼"));
// 年級
DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("年級"));
// 學籍異動代碼
DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("最後異動代號"));
//學籍異動文字
DyWb.Cells[DyWb_index, 12].PutValue(Util.GetUpdateDocNumDoc(Record.GetAttribute("備查文號")));
//學籍異動文號
DyWb.Cells[DyWb_index, 13].PutValue(Util.GetUpdateDocNumNum(Record.GetAttribute("備查文號")));
// 學籍異動核准日期
DyWb.Cells[DyWb_index, 14].PutValue(Util.ConvertDateStr1(Record.GetAttribute("備查日期")));
//畢業證書字號
DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("畢業證書字號"));
//備註說明
DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("備註"));
}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
private void CheckForOpenFile()
{
//Open workbook
try
{
Stream s = File.Open("Routes.xlsx", FileMode.Open, FileAccess.Read, FileShare.None);
s.Close();
routeBook = reader.Workbooks.Open(string.Format("{0}\\Routes.xlsx", System.IO.Directory.GetCurrentDirectory()));
dataSheet = routeBook.Worksheets[3];
ropeSheet = routeBook.Worksheets[2];
boulderSheet = routeBook.Worksheets[1];
firstTime = false;
}
catch (FileNotFoundException)
{
routeBook = reader.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
boulderSheet = routeBook.Worksheets[1];
boulderSheet.Name = "Boulders";
ropeSheet = routeBook.Worksheets.Add(After: boulderSheet);
ropeSheet.Name = "Ropes";
dataSheet = routeBook.Worksheets.Add(After: ropeSheet);
dataSheet.Name = "Data";
boulderSheet.Cells[1, "A"].Value = "Grade";
boulderSheet.Cells[1, "B"].Value = "Wall";
boulderSheet.Cells[1, "C"].Value = "Setter";
boulderSheet.Cells[1, "D"].Value = "Color";
ropeSheet.Cells[1, "A"].Value = "Grade";
ropeSheet.Cells[1, "B"].Value = "Wall";
ropeSheet.Cells[1, "C"].Value = "Setter";
ropeSheet.Cells[1, "D"].Value = "Color";
dataSheet.Cells[1, "A"].Value = "Boulder";
dataSheet.Cells[1, "B"].Value = "Ropes";
dataSheet.Cells[1, "C"].Value = "Setters";
dataSheet.Cells[1, "D"].Value = "Colors";
dataSheet.Cells[1, "E"].Value = "Walls";
dataSheet.Cells[1, "F"].Value = "RWalls";
routeBook.SaveAs(string.Format("{0}\\Routes.xlsx", System.IO.Directory.GetCurrentDirectory()));
}
catch (System.IO.IOException)
{
MessageBox.Show("Program cannot open if the spreadsheet is open elsewhere. Please close spreadsheet and try again.");
Environment.Exit(0);
}
UpdateOptions();
UpdateChart(boulderSheet, boulderChart, true);
UpdateChart(ropeSheet, ropeChart, true);
}
public void Dispose()
{
workbook = null;//为了便于后期合并cell,先不清空--wxj20181011
sheet = null;
}
public static void OpenSpreadsheetDocumentReadonly(string filepath)
{
// Open a SpreadsheetDocument based on a filepath.
Workbook workbook = new Workbook(filepath);
}
SaveGraphToNodeXLWorkbook
(
DateTime oStartTime,
XmlDocument oXmlDocument,
String sNetworkConfigurationFilePath,
String sNetworkFileFolderPath,
Boolean bAutomate,
Application oExcelApplication,
out String sWorkbookPath
)
{
Debug.Assert(oXmlDocument != null);
Debug.Assert(!String.IsNullOrEmpty(sNetworkConfigurationFilePath));
Debug.Assert(!String.IsNullOrEmpty(sNetworkFileFolderPath));
Debug.Assert(oExcelApplication != null);
// Create a new workbook from the NodeXL template.
String sNodeXLTemplatePath;
if (!ExcelTemplate.ApplicationUtil.TryGetTemplatePath(
out sNodeXLTemplatePath))
{
throw new SaveGraphToNodeXLWorkbookException(
ExitCode.CouldNotFindNodeXLTemplate, String.Format(
"The NodeXL Excel template file couldn't be found. It's"
+ " supposed to be at {0}, but it's not there. Is the NodeXL"
+ " Excel Template installed? It's required to run this"
+ " program."
,
sNodeXLTemplatePath
));
}
Workbook oNodeXLWorkbook = null;
try
{
oNodeXLWorkbook = oExcelApplication.Workbooks.Add(
sNodeXLTemplatePath);
}
catch (Exception oException)
{
OnException(oException,
ExitCode.CouldNotCreateNodeXLWorkbook,
"A NodeXL workbook couldn't be created."
);
}
// Create a NodeXL graph from the XML document.
IGraph oGraph = (new GraphMLGraphAdapter()).LoadGraphFromString(
oXmlDocument.OuterXml);
try
{
// Import the graph into the workbook.
//
// Note that the GraphMLGraphAdapter stored String arrays on the
// IGraph object that specify the names of the attributes that it
// added to the graph's edges and vertices. These get used by the
// ImportGraph method to determine which columns need to be added
// to the edge and vertex worksheets.
GraphImporter oGraphImporter = new GraphImporter();
oGraphImporter.ImportGraph(oGraph,
( String[] )oGraph.GetRequiredValue(
ReservedMetadataKeys.AllEdgeMetadataKeys,
typeof(String[])),
( String[] )oGraph.GetRequiredValue(
ReservedMetadataKeys.AllVertexMetadataKeys,
typeof(String[])),
false, oNodeXLWorkbook);
// Store the graph's directedness in the workbook.
PerWorkbookSettings oPerWorkbookSettings =
new ExcelTemplate.PerWorkbookSettings(oNodeXLWorkbook);
oPerWorkbookSettings.GraphDirectedness = oGraph.Directedness;
if (bAutomate)
{
// Store an "automate tasks on open" flag in the workbook,
// indicating that task automation should be run on it the next
// time it's opened. (It is up to the caller of this method to
// open the workbook to trigger automation.)
oPerWorkbookSettings.AutomateTasksOnOpen = true;
}
}
catch (Exception oException)
{
OnException(oException,
ExitCode.CouldNotImportGraphIntoNodeXLWorkbook,
"The network couldn't be imported into the NodeXL workbook."
);
}
// Save the workbook. Sample workbook path:
//
// C:\NetworkConfiguration_2010-06-01_02-00-00.xlsx
sWorkbookPath = FileUtil.GetOutputFilePath(oStartTime,
sNetworkConfigurationFilePath, sNetworkFileFolderPath,
String.Empty, "xlsx");
Console.WriteLine(
"Saving the network to the NodeXL workbook \"{0}\"."
,
sWorkbookPath
);
try
{
oNodeXLWorkbook.SaveAs(sWorkbookPath, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception oException)
{
OnException(oException, ExitCode.SaveNetworkFileError,
"The NodeXL workbook couldn't be saved."
);
}
try
{
oNodeXLWorkbook.Close(false, Missing.Value, Missing.Value);
}
catch (Exception oException)
{
OnException(oException, ExitCode.SaveNetworkFileError,
"The NodeXL workbook couldn't be closed."
);
}
}
private void VerileriYukle(string fileName)
{
DokumAnalizSonucListe.Clear();
Workbook workbook = new Workbook();
workbook.LoadDocument(fileName);
var ws = workbook.Worksheets[0];
var usedRange = ws.GetUsedRange();
for (int i = 2; i < usedRange.RowCount; i++)
{
var tarih = DateTime.Parse(ws.Cells[i, 0].Value.ToString());
var bolge = ws.Cells[i, 1].Value.ToString();
var elemanSayisi = int.Parse(ws.Cells[i, 2].Value.ToString());
var bobinNo = ws.Cells[i, 2].Value.ToString();
//Si Fe Cu Mn Mg Ti Zn Al
var Si = decimal.Parse(ws.Cells[i, 8].Value.ToString());
var Fe = decimal.Parse(ws.Cells[i, 15].Value.ToString());
var Cu = decimal.Parse(ws.Cells[i, 17].Value.ToString());
var Mn = decimal.Parse(ws.Cells[i, 14].Value.ToString());
var Mg = decimal.Parse(ws.Cells[i, 7].Value.ToString());
var Ti = decimal.Parse(ws.Cells[i, 11].Value.ToString());
var Zn = decimal.Parse(ws.Cells[i, 18].Value.ToString());
var Al = decimal.Parse(ws.Cells[i, 26].Value.ToString());
var s1 = new DokumAnalizSonuc
{
TarihSaat = tarih,
Bolge = bolge.Split('-')[0].Trim(),
DokumHatti = bolge.Split('-')[1].Trim(),
Alasim = bolge.Split('-')[2].Trim(),
BobinNo = bobinNo,
Si = Si,
Fe = Fe,
Cu = Cu,
Mn = Mn,
Mg = Mg,
Ti = Ti,
Zn = Zn,
Al = Al
};
DokumAnalizSonucListe.Add(s1);
}
workbook.Dispose();
SinirHatalariYukle();
}
// 导出账号
public string UserInfoExport(string tenantId, string brandId)
{
List <UserInfo> list = masterService.GetUserInfo(tenantId, brandId, "", "", "", "", "", "");
Workbook book = Workbook.Load(basePath + @"\Excel\" + "UserInfo.xlsx", false);
//填充数据
Worksheet sheet = book.Worksheets[0];
int rowIndex = 0;
foreach (UserInfo item in list)
{
//账号
sheet.GetCell("A" + (rowIndex + 2)).Value = item.AccountId;
//姓名
sheet.GetCell("B" + (rowIndex + 2)).Value = item.AccountName;
//密码
sheet.GetCell("C" + (rowIndex + 2)).Value = item.Password;
//权限
string roleTypeName = "";
if (item.RoleType == "B_Brand")
{
roleTypeName = "厂商";
}
else if (item.RoleType == "B_Bussiness")
{
roleTypeName = "业务";
}
else if (item.RoleType == "B_WideArea")
{
roleTypeName = "广域区域";
}
else if (item.RoleType == "B_BigArea")
{
roleTypeName = "大区";
}
else if (item.RoleType == "B_MiddleArea")
{
roleTypeName = "中区";
}
else if (item.RoleType == "B_SmallArea")
{
roleTypeName = "小区";
}
else if (item.RoleType == "B_Shop")
{
roleTypeName = "经销商";
}
else if (item.RoleType == "B_Group")
{
roleTypeName = "集团";
}
sheet.GetCell("D" + (rowIndex + 2)).Value = roleTypeName;
//Email
sheet.GetCell("E" + (rowIndex + 2)).Value = item.Email;
//Tel
sheet.GetCell("F" + (rowIndex + 2)).Value = item.TelNO;
// useChk
if (item.UseChk == true)
{
sheet.GetCell("G" + (rowIndex + 2)).Value = "Y";
}
else
{
sheet.GetCell("G" + (rowIndex + 2)).Value = "N";
}
rowIndex++;
}
//保存excel文件
string fileName = "账号" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
string dirPath = basePath + @"\Temp\";
DirectoryInfo dir = new DirectoryInfo(dirPath);
if (!dir.Exists)
{
dir.Create();
}
string filePath = dirPath + fileName;
book.Save(filePath);
return(filePath.Replace(basePath, ""));;
}
// Export Data to Excel
private void exportDataToExcel()
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel WorkBook|*.xls";
sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); // Save to MyDocuments
try
{
if (sfd.ShowDialog() == true)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = app.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)app.ActiveSheet;
app.Visible = false;
ws.Cells[1, 1] = "Product No";
ws.Cells[1, 2] = "Item";
ws.Cells[1, 3] = "Brand";
//ws.Cells[1, 4] = "Date of Purchase";
ws.Cells[1, 4] = "RP";
ws.Cells[1, 5] = "Quantity";
ws.Cells[1, 6] = "Total";
int i = 2;
// Start a Query
string sql = "SELECT * FROM datasalesinventory";
conn.query(sql);
conn.Open();
MySqlDataReader reader = conn.read();
if (reader.HasRows)
{
while (reader.Read())
{
string[] row = { reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7) };
long SalesNo = Convert.ToInt64(row[1]); // sales No
string SalesItem = row[2]; // Sales item
string SalesBrand = row[3];
int SalesRp = int.Parse(row[5]);
int SalesQty = int.Parse(row[6]);
int SalesTotal = int.Parse(row[7]);
mySales.Add(new Sales {
salesNo = SalesNo, salesItem = SalesItem, salesBrand = SalesBrand, salesRP = SalesRp, salesQty = SalesQty, salesTotal = SalesTotal
});
}
}
else
{
MessageBox.Show("No Rows", "Notice", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
reader.Close();
reader.Dispose();
conn.Close();
// End of Query
//string das = listViewSales.Items[0].ToString();
foreach (Sales item in mySales)
{
//ws.Cells[i, 1] = (string)((DataRowView)listViewSales.SelectedItems[0])["refNo"];
ws.Cells[i, 1] = item.salesNo.ToString();
ws.Cells[i, 2] = item.salesItem;
ws.Cells[i, 3] = item.salesBrand;
ws.Cells[i, 4] = item.salesRP.ToString();
ws.Cells[i, 5] = item.salesQty.ToString();
ws.Cells[i, 6] = item.salesTotal.ToString();
i++;
} // Closing of Foreach
wb.SaveAs(sfd.FileName, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
app.Quit();
mySales.Clear(); // Clear All Items
MessageBox.Show("Your data has been successfully exported", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
} // Closing of IF Statement
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public void Store(Workbook workbookContext)
{
Logger.Info("Store with workbook context");
_preset.Store(workbookContext);
}
private void CreateFiche()
{
if (!IsExcelInstalled)
{
MessageBox.Show("Excel n'est pas installé sur cette machine!\nVeuillez installer Excel ou contactez 'Marouane Lyousfi' pour plus d'information", "Excel manqué", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
wb = excel.Workbooks.Open(this.path);
ws = wb.Worksheets[1];
var picture = ws.Shapes.Item(2);
leftPosition = picture.Left;
topPosition = picture.Top;
height = picture.Height;
width = picture.Width;
List <string> file = Directory.GetFiles(model.EmployerFolder).ToList();
string photo = file.SingleOrDefault(f => f.Contains("Photo."));
if (photo == null)
{
if (!ResultBox("Photo manquée", "Cet employé n'as pas de photo de profil\nVoulez-vous continuer avec la photo par défault? "))
{
return;
}
}
else
{
picture.Delete();
ws.Shapes.AddPicture(photo, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, leftPosition, topPosition, width, height);
}
ws.Cells[18, 7].value2 = model.numMatricul;
ws.Cells[22, 5].value2 = model.nom;
ws.Cells[24, 5].value = model.prenom;
ws.Cells[26, 5].value2 = model.adresse;
ws.Cells[31, 5].value2 = model.gsm;
ws.Cells[33, 5].value2 = model.cin;
ws.Cells[35, 5].value2 = model.cnss;
ws.Cells[37, 5].value2 = model.rib;
ws.Cells[39, 5].value2 = model.site;
PrintingPDF(Path.Combine(model.EmployerFolder, "Fiche.pdf"));
dc.LoadFiles(dc.comboBox1.Text);
wb.Close(false);
Process.Start(Path.Combine(model.EmployerFolder, "Fiche.pdf"));
}
catch (Exception EX)
{
MessageBox.Show(EX.Message, "Excel manqué", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (((Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast <Microsoft.Office.Interop.Excel.Workbook>().FirstOrDefault(x => x.Name == "Fiche.xlsx") != null)
{
wb.Close(false);
}
}
finally
{
if (((Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast <Microsoft.Office.Interop.Excel.Workbook>().FirstOrDefault(x => x.Name == "Fiche.xlsx") != null)
{
wb.Close(false);
}
}
}
public byte[] ExportarPesquisa(AlunoFilter filter)
{
List <AlunoDTO> alunos = alunoRepository.FiltrarAlunos(filter).Select(x => new AlunoDTO(x)).ToList();
#region Criação da tabela
Workbook conteudoExcel = new Workbook
{
FileName = "exportação.xlsx"
};
conteudoExcel.Worksheets.RemoveAt(0);
Worksheet sheet = conteudoExcel.Worksheets.Add("Alunos");
string[] propertyNames =
{
/* 00 */ "Nome",
/* 01 */ "CPF",
/* 02 */ "Telefone",
/* 03 */ "Celular",
/* 04 */ "Matrículas",
/* 05 */ "Turmas",
/* 06 */ "Validade",
/* 07 */ "Situação"
};
Style styleDia = conteudoExcel.CreateStyle();
styleDia.Custom = "dd/mm/yyyy";
#endregion
#region Preenchimento da tabela
var contador = 1;
alunos.ForEach(a =>
{
try
{
var row = sheet.Cells.Rows[contador];
GravarValor(row, a.Nome, 0);
GravarValor(row, a.CPF, 1);
GravarValor(row, a.Telefone, 2);
GravarValor(row, a.Celular, 3);
GravarValor(row, string.Join(", ", a.TurmasAluno.Select(ta => ta.Matricula)), 4);
GravarValor(row, string.Join(", ", a.TurmasAluno.Select(ta => ta.Turma.Codigo)), 5);
GravarValor(row, a.DataValidade, 6, styleDia);
GravarValor(row, a.TipoStatusAluno, 7);
contador++;
}
catch (Exception e)
{
log.Error($"Erro exportar pesquisa.", e);
}
});
sheet.ListObjects.Add(0, 0, contador - 1, propertyNames.Length - 1, true);
var header = sheet.Cells.Rows[0];
for (int i = 0; i < propertyNames.Length; i++)
{
header[i].PutValue(propertyNames[i]);
}
sheet.AutoFitColumns();
sheet.AutoFitRows();
#endregion
MemoryStream stream = new MemoryStream();
conteudoExcel.Save(stream, SaveFormat.Xlsx);
stream.Seek(0, SeekOrigin.Begin);
return(stream.ToArray());
void GravarValor(Row row, object dado, int indice, Style style = null)
{
if (dado == null)
{
dado = "";
}
row[indice].PutValue(dado);
if (style != null)
{
row[indice].SetStyle(style);
}
}
}
protected void exportToExcel()
{
string file = "";
try
{
DateTime dtFrom = DateTime.Today, dtTo = DateTime.Today;
if (Convert.ToString(txtFromDate.Text) != "" && Convert.ToString(txtFromDate.Text) != null)
{
dtFrom = DateTime.Parse(txtFromDate.Text);
}
if (Convert.ToString(txtToDate.Text) != "" && Convert.ToString(txtToDate.Text) != null)
{
dtTo = DateTime.Parse(txtToDate.Text);
}
if (dtTo < dtFrom)
{
divError.InnerHtml = "<b> <font color=\"red\"> Please select proper To and From Dates. </font> </b>";
}
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
file = ConfigurationManager.ConnectionStrings["FilePath"].ConnectionString + "Report.xls"; //"C:\\Report.xls"; // +DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + ".xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
string[] colNames = { "Id", "Sent Date", "Company Website", "Company Name", "Client EmailId", "Client Name", "Client Phone" }; // "Company Phone"
for (int i = 0; i < colNames.Length; i++)
{
worksheet.Cells[0, i] = new Cell(colNames[i]);
worksheet.Cells.ColumnWidth[0, (ushort)i] = 5000;
}
DataSet ds = new DataSet("dsEmailsInfo");
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("sp_getEmailsInfo", con);
if (dtFrom == DateTime.Today && dtTo == DateTime.Today) //From and To dates are null for getting all records
{
cmd.Parameters.AddWithValue("@startDate", DBNull.Value);
cmd.Parameters.AddWithValue("@endDate", DBNull.Value);
}
else if (dtFrom == DateTime.Today && dtTo != DateTime.Today) //From and To dates are null for getting all records
{
cmd.Parameters.AddWithValue("@startDate", DBNull.Value);
cmd.Parameters.AddWithValue("@endDate", dtTo);
}
else
{
cmd.Parameters.AddWithValue("@startDate", dtFrom);
cmd.Parameters.AddWithValue("@endDate", dtTo);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "EmailsInfo");
DataTable dt = ds.Tables[0];
int intRowNo = 1;
int intSrNo = 1;
foreach (DataRow row in dt.Rows)
{
intRowNo++;
int i = 0;
foreach (DataColumn col in dt.Columns)
{
if (i == 0)
{
worksheet.Cells[intRowNo, i++] = new Cell(Convert.ToString(intSrNo++));
worksheet.Cells.ColumnWidth[(ushort)intRowNo, (ushort)i] = 1000;
}
else
{
worksheet.Cells[intRowNo, i++] = new Cell(Convert.ToString(row[col]));
worksheet.Cells.ColumnWidth[(ushort)intRowNo, (ushort)i] = 6000;
}
}
}
}
workbook.Worksheets.Add(worksheet);
//-----------------------------------------
worksheet = new Worksheet("Second Sheet");
for (int i = 0; i < 550; i++)
{
worksheet.Cells[i, 0] = new Cell(i);
}
workbook.Worksheets.Add(worksheet);
//-----------------------------------------------------------
workbook.Save(file);
string name = file.Substring((file.LastIndexOf("\\") + 1));
string fileName = "Report" + DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + ".xls";
string type = "application/vnd.ms-excel";
if (true)
{
Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);
}
if (type != "")
{
Response.ContentType = type;
Response.WriteFile(file);
Response.End();
}
}
catch (Exception ex)
{
//btnScanEmails.Visible = false;
//btnExportToExcel.Visible = false;
tblLogin.Visible = false;
tblEmails.Visible = false;
divError.InnerText = Convert.ToString(ex);
}
}
public System.Data.DataTable checkIfFileInformationStored(string filePath)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook = excel.Workbooks.Open(filePath);
Worksheet worksheet = workbook.Worksheets[1];
SqlConnection sqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=ImportFileData;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
sqlConn.Open();
string getEveryRow = "Select * From [StoredColumns]";
SqlDataAdapter sda = new SqlDataAdapter(getEveryRow, sqlConn);
System.Data.DataTable dtb = new System.Data.DataTable();
sda.Fill(dtb);
if (dtb.Rows.Count > 0)
{
foreach (DataRow row in dtb.Rows)
{
int trasactionsRow = int.Parse(row["TransStartRow"].ToString());
string accountNumberPosString = row["AccountNumberPos"].ToString();
string dateColumnString = row["DateColumn"].ToString();
string priceColumnString = row["PriceColumn"].ToString();
string balanceColumnString = row["BalanceColumn"].ToString();
string commentColumnString = row["CommentColumn"].ToString();
int dateColumn;
try
{
dateColumn = int.Parse(dateColumnString);
}
catch (Exception e)
{
dateColumn = ExcelColumnNameToNumber(dateColumnString);
}
int balanceColumn = -1;
if (dateColumnString != "None")
{
try
{
balanceColumn = int.Parse(balanceColumnString);
}
catch (Exception e)
{
balanceColumn = ExcelColumnNameToNumber(balanceColumnString);
}
}
List <int> accountNumberPos = new List <int>();
// if it has 2 elements its in a cell
// if it has 1 element it is a column
if (accountNumberPosString != "Sheet name")
{
int tempValue1 = 0;
long size = sizeof(char) * accountNumberPosString.Length;
//todo
if (size > 1)//its a cell
{
int tempValue2 = 0;
try
{
tempValue1 = int.Parse(accountNumberPosString[1].ToString());
}
catch (Exception e)
{
tempValue1 = ExcelColumnNameToNumber(accountNumberPosString[1].ToString());
}
try
{
tempValue2 = int.Parse(accountNumberPosString[0].ToString());
}
catch (Exception e)
{
tempValue2 = ExcelColumnNameToNumber(accountNumberPosString[0].ToString());
}
accountNumberPos.Add(tempValue1);
accountNumberPos.Add(tempValue2);
}
else if (size == 1)
{
try
{
tempValue1 = int.Parse(accountNumberPosString);
}
catch (Exception e)
{
balanceColumn = ExcelColumnNameToNumber(accountNumberPosString);
}
accountNumberPos.Add(tempValue1);
}
}
else
{
accountNumberPos = null;
}
List <int> commentColumns = new List <int>();
string[] commentColumnsSplitted = commentColumnString.Split(',');
for (int i = 0; i < commentColumnsSplitted.Length; i++)
{
int tempValue;
try
{
tempValue = int.Parse(commentColumnsSplitted[i]);
}
catch (Exception e)
{
tempValue = ExcelColumnNameToNumber(commentColumnsSplitted[i]);
}
commentColumns.Add(tempValue);
}
List <int> priceColumn = new List <int>();
string[] priceColumnsSplitted = priceColumnString.Split(',');
if (priceColumnsSplitted.Length > 1)
{
for (int i = 0; i < priceColumnsSplitted.Length; i++)
{
int tempValue;
try
{
tempValue = int.Parse(priceColumnsSplitted[i]);
}
catch (Exception e)
{
tempValue = ExcelColumnNameToNumber(priceColumnsSplitted[i]);
}
priceColumn.Add(tempValue);
}
}
else
{
int tempValue;
try
{
tempValue = int.Parse(priceColumnsSplitted[0]);
}
catch (Exception e)
{
tempValue = ExcelColumnNameToNumber(priceColumnsSplitted[0]);
}
priceColumn.Add(tempValue);
}
}
return(dtb);
}
return(null);
}
