NOTE: proprietary info replaced with "xxx" where applicable.
- Code: Select all
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Spire.Xls;
using System.IO;
namespace ConvertCSVToPDF
{
class Program
{
static void Main(string[] args)
{
Spire.License.LicenseProvider.SetLicenseFileFullPath(xxx);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
try
{
//Set directory where files for conversion will be found.
DirectoryInfo directory = new DirectoryInfo(xxx);
//For loop, look through all .csv files in specified location.
var files = directory.GetFiles("*.csv");
foreach (var file in files)
{
//Capture current filename
string filename = file.FullName.ToString();
//Define variable for workbook
Workbook workbook = new Workbook();
//Load the document from disk
workbook.LoadFromFile(filename, ",", 1, 1);
//Set the SheetFitToPage property as true
//workbook.ConverterSetting.SheetFitToPage = true;
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
foreach (Worksheet sheet2 in workbook.Worksheets)
{
//Change the page size
sheet2.PageSetup.PaperSize = PaperSizeType.PaperA3;
}
//Set left header
sheet.PageSetup.LeftHeader = "xxx";
//Set right header
sheet.PageSetup.RightHeader = "xxx";
//Set center header. Use fiscal quarter of run date.
int mth = DateTime.Now.Month;
if (mth <= 3)
{
sheet.PageSetup.CenterHeader = "Quarter 1 " + DateTime.Now.Year;
}
else if (mth > 3 && mth <= 6)
{
sheet.PageSetup.CenterHeader = "Quarter 2 " + DateTime.Now.Year;
}
else if (mth > 6 && mth <= 9)
{
sheet.PageSetup.CenterHeader = "Quarter 3 " + DateTime.Now.Year;
}
else if (mth > 9 && mth <= 12)
{
sheet.PageSetup.CenterHeader = "Quarter 4 " + DateTime.Now.Year;
}
sheet.ViewMode = ViewMode.Layout;
//Set center footer.
sheet.PageSetup.CenterFooter = "&P" + "/" + "&N";
//Get the cell range where you want to apply border style
CellRange cr = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn];
//Apply border style
cr.Borders.LineStyle = LineStyleType.Thin;
cr.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
cr.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
cr.Borders.Color = Color.Black;
//Set specific column widths where necessary
//to preserve formatting as defined by business owner.
sheet.SetColumnWidthInPixels(1, 80);
sheet.SetColumnWidthInPixels(2, 100);
sheet.SetColumnWidthInPixels(3, 100);
sheet.SetColumnWidthInPixels(4, 80);
sheet.SetColumnWidthInPixels(5, 200);
sheet.SetColumnWidthInPixels(6, 80);
sheet.SetColumnWidthInPixels(7, 75);
sheet.SetColumnWidthInPixels(8, 100);
sheet.SetColumnWidthInPixels(9, 75);
sheet.SetColumnWidthInPixels(10, 100);
//Wrap the excel text;
sheet.Range["E2:E1000"].Style.WrapText = true;
//Create a style
CellStyle style = workbook.Styles.Add("headerStyle");
//Apply border style
style.Borders.LineStyle = LineStyleType.Thin;
style.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
style.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
style.Borders.Color = Color.Black;
//Set the shading color
style.Color = Color.FromArgb(217, 217, 217);
//Set the font color
style.Font.Color = Color.Black;
//Set font size
style.Font.Size = 11;
//Set bold for the font
style.Font.IsBold = true;
//Set alignment
style.HorizontalAlignment = HorizontalAlignType.Center;
style.VerticalAlignment = VerticalAlignType.Center;
//Set the style for the specific range
workbook.Worksheets[0].Range["A1:J1"].CellStyleName = style.Name;
//Save to PDF document
string output = filename.Replace(".csv", ".pdf");
workbook.SaveToFile(output, FileFormat.PDF);
//Delete the CSV file
File.Delete(filename);
}
}
catch (DirectoryNotFoundException dirNotFound)
{
Console.WriteLine(dirNotFound.Message);
}
}
}
}