public tempJson EPPFiletoWS(FileInfo fi, IFormFile file)
{
tempJson tempjson = new tempJson();
switch (fi.Extension)
{
case ".xlsx":
using (ExcelPackage ex = new ExcelPackage())
{
ExcelWorksheet ws = ex.Workbook.Worksheets.Add("Sheet 1");
ExcelWorksheet ws2 = ex.Workbook.Worksheets.Add("Sheet 2"); // Create WS
var format = new ExcelTextFormat {
Delimiter = '\t', EOL = "\r"
};
ws.Cells["A1"].LoadFromText(fi, format);
WStoExport(ex, tempjson);
}
break;
case ".csv":
break;
}
return(null);
}
static void Main(string[] args)
{
string csvFileName = @"FL_insurance_sample.csv";
string excelFileName = @"FL_insurance_sample.xls";
string worksheetsName = "TEST";
bool firstRowIsHeader = false;
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\r"; // DEFAULT IS "\r\n";
// format.TextQualifier = '"';
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
Console.WriteLine("Finished!");
Console.ReadLine();
}
public static void Convert(string name)
{
string csvFileName = name;
string[] temp_name = name.Split(".");
string excelFileName = temp_name[0] + ".xlsx";
string worksheetsName = temp_name[0];
bool firstRowIsHeader = false;
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\r"; // DEFAULT IS "\r\n";
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
Console.WriteLine("Finished converting " + csvFileName + " to " + excelFileName + ".");
}
public static byte[] CSVToExcel(byte[] csvbytes, string path)
{
string csvFileName = path + "\\csvfile.csv";
string excelFileName = path + "\\excelfile.xlsx";
byte[] bytes;
try
{
File.WriteAllBytes(csvFileName, csvbytes);
string worksheetsName = "sheet1";
bool firstRowIsHeader = true;
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\r";
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
bytes = File.ReadAllBytes(excelFileName);
}
File.Delete(csvFileName);
File.Delete(excelFileName);
}
catch
{
bytes = null;
File.Delete(csvFileName);
File.Delete(excelFileName);
}
return(bytes);
}
private string CSV2Excel(byte[] fileContents)
{
Directory.CreateDirectory(Server.MapPath("~/CSV2Excel"));
string csvFileName = Server.MapPath("~/CSV2Excel/" + @"CSV2Excel_CSV_" + DateTime.Now.ToFileTimeUtc() + ".csv");
System.IO.File.WriteAllBytes(csvFileName, fileContents);
string excelFileName = Server.MapPath("~/CSV2Excel/" + @"CSV2Excel_Excel_" + DateTime.Now.ToFileTimeUtc() + ".xlsx");
string worksheetsName = "Armazém 3D";
bool firstRowIsHeader = true;
var format = new ExcelTextFormat();
format.Delimiter = ';';
format.EOL = "\r"; // DEFAULT IS "\r\n";
format.SkipLinesEnd = 1; //Remove ultima linha (essa ultima fica em branco)
// format.TextQualifier = '"';
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
return(excelFileName);
}
private void FindBasePriceXLSX(string path)
{
FileInfo File = new FileInfo(path);
using (ExcelPackage pck = new ExcelPackage(File))
{
var format = new ExcelTextFormat();
format.Delimiter = '/';
ExcelWorksheet ws = null;
ws = pck.Workbook.Worksheets[1];
var faceRow =
from cell in ws.Cells["a:aa"]
where cell.Value?.ToString() == typeFace
select cell;
var tableRow =
from cell in ws.Cells["a:aa"]
where cell.Value?.ToString() == typeTable
select cell;
int i = tableRow.ToList()[0].Start.Row;
int j = faceRow.ToList()[0].Start.Column;
string tmp = ws.Cells[i, j].Value.ToString();
basePrice = (int)double.Parse(tmp);
}
}
public Excel(string pathToWorkBook)
{
try
{
if (pathToWorkBook.Contains(".csv"))
{
convertedWorkbook = $"{pathToWorkBook.Substring(0,pathToWorkBook.Length-4)} Converted.xlsx";
string worksheetName = "Converted CSV";
ExcelTextFormat format = new ExcelTextFormat();
format.Delimiter = ',';
using (ExcelPackage package = new ExcelPackage(new FileInfo(convertedWorkbook)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(pathToWorkBook), format).Style.Numberformat.Format = "@";
package.Save();
workbookDestination = package.File;
}
workbook = new XLWorkbook(convertedWorkbook);
worksheet = workbook.Worksheet(1);
}
else
{
workbook = new XLWorkbook(pathToWorkBook);
worksheet = workbook.Worksheet(1);
}
}
catch (IOException)
{
MessageBox.Show("Excel Sheet is in use. Please close it and try again.");
throw new IOException();
}
}
private static void PutExcel(FileInfo newFile)
{
using (var excel = new ExcelPackage(newFile))
{
ExcelTextFormat f = new ExcelTextFormat
{
TextQualifier = '"',
Delimiter = ','
};
// do work here
ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Regression_Tables");
bool writtenHeader = false;
StringBuilder csv = new StringBuilder();
foreach (var t in _tables)
{
var c = TableToCsv(t, escapeCsv: false, writeHeader: writtenHeader == false);
writtenHeader = true;
foreach (var line in c)
{
csv.AppendLine(line);
}
}
worksheet.Cells.LoadFromText(csv.ToString(), f, TableStyles.Dark10, true);
excel.Save();
}
}
//从CSV导入数据到表
static void loadCsv()
{
string csvText = "Id,Name,Quantity,Price,Value\n10001,sss,12,12,32\n10002,lll,14,14,14";
//设置csv解析格式
ExcelTextFormat format = new ExcelTextFormat
{
//换行符
EOL = "\n",
//字段分隔符
Delimiter = ',',
//解析时，跳过的行数
SkipLinesBeginning = 1,
};
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
//读取文件中的第1个sheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
//找到当前文件的最后一行
int endRow = worksheet.Dimension.End.Row;
//从新的一行开始写入数据
worksheet.Cells[endRow + 1, 1].LoadFromText(csvText, format);
package.Save();
}
Console.WriteLine("Load CSV finish.");
}
/// <summary>
/// Creates an XLS file from the given path to an existing csv file.
/// </summary>
/// <param name="csv_path">The path to the csv file.</param>
/// <returns>The name of the new xlsx file (including its path).</returns>
public string CreateXLSXFile(string csv_path)
{
//Get the file name and create a new name for the xlsx file.
string fileName = csv_path.Split('.')[0];
string newFileName = fileName + ".xlsx";
//Set formatting and delimiter values.
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\r";
//Check if the new file exists already. If so, delete it.
if (File.Exists(newFileName))
{
File.Delete(newFileName);
}
//Create a new package with the new file name.
using (ExcelPackage package = new ExcelPackage(new FileInfo(newFileName)))
{
//Add a new worksheet into the file.
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Raw Conversion Data");
//Copy the data from the csv int othe new sheet.
worksheet.Cells["A1"].LoadFromText(new FileInfo(csv_path), format, OfficeOpenXml.Table.TableStyles.Medium27, false);
ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("Treated Data");
//Save the package changes.
package.Save();
}
//return the name of the new file (including its path).
return(newFileName);
}
private static DataTable readCSV(string path, bool header = true, char delimiter = ',')
{
DataTable dt = new DataTable();
ExcelPackage.LicenseContext = LicenseContext.Commercial;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//set the formatting options
ExcelTextFormat format = new ExcelTextFormat();
format.Delimiter = delimiter;
format.Culture = new CultureInfo(Thread.CurrentThread.CurrentCulture.ToString());
format.Culture.DateTimeFormat.ShortDatePattern = "dd-mm-yyyy";
format.Encoding = new UTF8Encoding();
//read the CSV file from disk
FileInfo file = new FileInfo(path);
//create a new Excel package
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
//load the CSV data into cell A1
worksheet.Cells["A1"].LoadFromText(file, format);
readXLS(ref worksheet, ref dt, header);
}
return(dt);
}
private void WriteExcelFiles(string outputDir)
{
var files = Directory.GetFiles(outputDir, "*.csv");
foreach (var csvFile in files)
{
string excelFile = Path.Combine(outputDir, Path.GetFileNameWithoutExtension(csvFile) + ".xls");
if (File.Exists(excelFile))
{
File.Delete(excelFile);
}
string worksheetsName = "TEST";
bool firstRowIsHeader = false;
var excelTextFormat = new ExcelTextFormat();
excelTextFormat.Delimiter = ',';
excelTextFormat.TextQualifier = '"';
var excelFileInfo = new FileInfo(excelFile);
var csvFileInfo = new FileInfo(csvFile);
using (ExcelPackage package = new ExcelPackage(excelFileInfo))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(csvFileInfo, excelTextFormat, OfficeOpenXml.Table.TableStyles.Medium25, firstRowIsHeader);
package.Save();
}
}
}
public IActionResult ExportExcelFromCsv()
{
var csv = GenerateCsv();
using var pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sheet1");
var format = new ExcelTextFormat
{
DataTypes = new[]
{
eDataTypes.String, eDataTypes.String
},
Delimiter = Delimiter.First(),
Encoding = new UTF8Encoding(),
};
using var range = ws.Cells[1, 1];
range.LoadFromText(csv, format);
var bytes = pck.GetAsByteArray();
return(this.File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "menus.xlsx",
true));
}
public void Initialize()
{
_package = new ExcelPackage();
_worksheet = _package.Workbook.Worksheets.Add("test");
_lines = new StringBuilder();
_format = new ExcelTextFormat();
}
private void btnEXPORTAREXCEL_Click(object sender, EventArgs e)
{
StreamWriter sw = new StreamWriter(ubicacionExport + "\\PP.csv");
for (int i = 0; i < listVisible.Count; i++)
{
sw.Write(listVisible[i].HeaderText);
if (i != listVisible.Count)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataGridViewRow dr in dgvMATERIALESPP.Rows)
{
for (int i = 0; i < listVisible.Count; i++)
{
sw.Write(dr.Cells[i].Value);
if (i != listVisible.Count)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Flush();
sw.Close();
/////// csv a xls
///
string csvFileName = @ubicacionExport + "\\PP.csv";
string excelFileName = @ubicacionExport + "\\PP.xls";
string worksheetsName = "PP_" + DateTime.Now.ToString();
bool firstRowIsHeader = false;
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\r"; // DEFAULT IS "\r\n";
// format.TextQualifier = '"';
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
Console.WriteLine("Finished!");
Console.ReadLine();
MessageBox.Show("Exportado con exito en: " + ubicacionExport, "Notificación", MessageBoxButtons.OK);
}
private static void PutExcel1(FileInfo newFile)
{
using (var excel = new ExcelPackage(newFile))
{
ExcelTextFormat f = new ExcelTextFormat
{
TextQualifier = '"',
Delimiter = ','
};
var worksheet = excel.Workbook.Worksheets.Add("Regression_Tables");
worksheet.Cells.Style.Font.Name = "Times New Roman";
worksheet.Cells.Style.Font.Size = 12;
var colA = worksheet.Column(1);
colA.Width = 120;
colA.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
var colB = worksheet.Column(2);
colB.Width = 50;
colB.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var colC = worksheet.Column(3);
colC.Width = 40;
colC.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var currentAddress = new ExcelAddress("A1");
const string col = "A";
ExcelAddress NextAddress(string column, int lastRow, int gapSize = 0) => new ExcelAddress(column + (lastRow + gapSize + 1));
foreach (var o in _orderedCommandsAndTables)
{
var command = _commands.FirstOrDefault(x => x.Offset == o.Offset);
if (command != null)
{
var range = PutCommandToWorksheet(worksheet, currentAddress, command);
range.Style.Font.Bold = true;
range.Style.WrapText = true;
currentAddress = NextAddress(col, range.End.Row);
continue;
}
var table = _tables.FirstOrDefault(x => x.Offset == o.Offset);
if (table != null)
{
var range = PutTableToWorksheet1(worksheet, currentAddress, table);
range.AutoFilter = false;
currentAddress = NextAddress(col, range.End.Row, gapSize: 1);
}
}
excel.Save();
}
}
//Akcija koja ce parsirati podatke iz upload-ovanog file-a podaci.csv i vratiti podatke o osobama
private static List <Klijent> UcitajPodatkeIzCSV()
{
List <Klijent> klijenti = new List <Klijent>();
string folderPath = ConfigurationManager.AppSettings["PathToUserData"];
if (folderPath == null)
{
throw new Exception("The required configuration key is missing. ");
}
string csvPath = folderPath + "/podaci.csv";
var result = string.Empty;
string worksheetsName = "data";
bool firstRowIsHeader = false;
var format = new ExcelTextFormat();
format.Delimiter = ';';
format.TextQualifier = '"';
using (FileStream stm = new
FileStream(csvPath, FileMode.Open,
FileAccess.Read, FileShare.None))
{
using (var reader = new StreamReader(stm))
using (ExcelPackage package = new ExcelPackage())
{
result = reader.ReadToEnd();
ExcelWorksheet worksheet =
package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(result, format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
for (int row = start.Row; row <= end.Row; row++)
{
Klijent klijent = new Klijent
{
Ime = worksheet.Cells[row, 1].Text,
Prezime = worksheet.Cells[row, 2].Text,
PostanskiBroj = worksheet.Cells[row, 3].Text,
Grad = worksheet.Cells[row, 4].Text,
Telefon = worksheet.Cells[row, 5].Text,
};
klijenti.Add(klijent);
}
}
}
return(klijenti);
}
private string[] GetLines(string text, ExcelTextFormat Format)
{
if (Format.EOL == null || Format.EOL.Length == 0)
{
return new string[] { text }
}
;
var eol = Format.EOL;
var list = new List <string>();
var inTQ = false;
var prevLineStart = 0;
for (int i = 0; i < text.Length; i++)
{
if (text[i] == Format.TextQualifier)
{
inTQ = !inTQ;
}
else if (!inTQ)
{
if (IsEOL(text, i, eol))
{
var s = text.Substring(prevLineStart, i - prevLineStart);
if (eol == "\n" && s.EndsWith("\r"))
{
s = s.Substring(0, s.Length - 1); //If EOL char is lf and last chart cr then we remove the trailing cr.
}
if (eol == "\r" && s.StartsWith("\n"))
{
s = s.Substring(1); //If EOL char is cr and last chart lf then we remove the heading lf.
}
list.Add(s);
i += eol.Length - 1;
prevLineStart = i + 1;
}
}
}
if (inTQ)
{
throw (new ArgumentException(string.Format("Text delimiter is not closed in line : {0}", list.Count)));
}
if (prevLineStart >= Format.EOL.Length && IsEOL(text, prevLineStart - Format.EOL.Length, Format.EOL))
{
//list.Add(text.Substring(prevLineStart- Format.EOL.Length, Format.EOL.Length));
list.Add("");
}
else
{
list.Add(text.Substring(prevLineStart));
}
return(list.ToArray());
}
public void Convert(string xlsPath, string csvPath)
{
ExcelPackage package = new ExcelPackage(new FileInfo(xlsPath));
string worksheetsName = Path.GetFileNameWithoutExtension(csvPath);
var format = new ExcelTextFormat();
format.Delimiter = Delimiter;
format.EOL = EndofLine;
format.Encoding = Encoding.UTF8;
string csvText = string.Empty;
foreach (var worksheet in package.Workbook.Worksheets)
{
for (int row = worksheet.Dimension.Start.Row; row <= worksheet.Dimension.End.Row; row++)
{
for (int column = worksheet.Dimension.Start.Column; column <= worksheet.Dimension.End.Column; column++)
{
if (worksheet.Cells[row, column].Value != null)
{
var strValue = worksheet.Cells[row, column].Value.ToString();
if (strValue.Contains(",") || strValue.Contains("\n")) // 가 있을때 Quotation 이라고 생각해서 "" 를 삽입한다.
{
csvText += string.Format("\"{0}\"", strValue);
}
else
{
csvText += strValue;
}
if (column != worksheet.Dimension.End.Column)
{
csvText += Delimiter; // 마지막 열에는 붙일 필요가 없다.
}
}
}
csvText += EndofLine;
}
}
package.Dispose();
File.Delete(xlsPath);
if (File.Exists(csvPath))
{
File.Delete(csvPath);
}
var sw = new StreamWriter(csvPath, false, Encoding.UTF8);
sw.WriteLine(csvText);
sw.Close();
}
public void EverfiExcelTemplate_ImportCSV_ThrowsFileNotFoundIfFileDoesNotExist()
{
FileInfo existingFilePath = new FileInfo("../../../Utilities/TestData/faketemplate.xlsx");
FileInfo nonExistingCSVFile = new FileInfo("../../../Utilities/TestData/nonexisting.csv");
var mockLogger = new Mock <ILogger>();
using (EverfiExcelTemplate excelTemplate = new EverfiExcelTemplate(existingFilePath, mockLogger.Object))
{
ExcelTextFormat format = new ExcelTextFormat();
Assert.Throws <FileNotFoundException>(() => excelTemplate.ImportCsv(format, nonExistingCSVFile));
}
}
public LoadFromText(ExcelRangeBase range, string text, LoadFromTextParams parameters)
{
_range = range;
_worksheet = range.Worksheet;
_text = text;
if (parameters.Format == null)
{
_format = new ExcelTextFormat();
}
else
{
_format = parameters.Format;
}
}
public void EverfiExcelTemplate_ImportCSV_ReturnsTrueIfDataIsImported()
{
FileInfo existingFilePath = new FileInfo("../../../Utilities/TestData/faketemplate.xlsx");
FileInfo csvFile = new FileInfo("../../../Utilities/TestData/data.csv");
bool csvDataImported = false;
var mockLogger = new Mock <ILogger>();
using (EverfiExcelTemplate excelTemplate = new EverfiExcelTemplate(existingFilePath, mockLogger.Object))
{
ExcelTextFormat format = new ExcelTextFormat();
csvDataImported = excelTemplate.ImportCsv(format, csvFile);
}
Assert.True(csvDataImported);
}
private static void AddData(ExcelWorksheet worksheet, List <List <string> > data)
{
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.TextQualifier = '"';
for (int i = 0; i < data.Count; i++)
{
for (int j = 0; j < data[i].Count; j++)
{
worksheet.Cells[i + 2, j + 1].LoadFromText('"' + data[i][j] + '"', format);
}
}
}
public static void EpPlusWriter2(string inFile, string outFile)
{
using (var book = new ExcelPackage())
{
var sheet = book.Workbook.Worksheets.Add("Sheet1");
var format = new ExcelTextFormat
{
TextQualifier = '\t',
};
sheet.Cells["A1"].LoadFromText(inFile, format);
book.File = new FileInfo(outFile);
book.Save();
}
}
private static ExcelPackage GetMarvelCsvPackage()
{
var path = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "Marvel.csv");
var file = new FileInfo(path);
var package = new ExcelPackage();
var textFormat = new ExcelTextFormat();
textFormat.TextQualifier = '"';
var sheet = package.Workbook.Worksheets.Add("Marvel");
sheet.Cells.LoadFromText(File.ReadAllText(file.FullName), textFormat);
return(package);
}
public static void ConvertTSVtoEXCEL(FileInfo fi1)
{
string worksheetsName = "sheet1";
var format = new ExcelTextFormat();
format.Delimiter = '\t';
format.EOL = "\r";
using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(TsvFileName), format);
package.Save();
}
}
private static void ConvertCsvToExcel(string excelFileName, string worksheetName, string csvFileName)
{
bool firstRowIsHeader = true;
var format = new ExcelTextFormat();
format.Delimiter = ';';
// format.EOL = "\r";
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
}
public void EverfiExcelTemplate_ImportCSV_ReturnsFalseIfSheetDoesNotExist()
{
FileInfo existingFilePath = new FileInfo("../../../Utilities/TestData/faketemplate.xlsx");
FileInfo csvFile = new FileInfo("../../../Utilities/TestData/data.csv");
string wrongSheetName = "Does Not Exist";
bool csvDataImported = true;
var mockLogger = new Mock <ILogger>();
using (EverfiExcelTemplate excelTemplate = new EverfiExcelTemplate(existingFilePath, mockLogger.Object))
{
ExcelTextFormat format = new ExcelTextFormat();
excelTemplate.TEMPLATE_SHEET_NAME = wrongSheetName;
csvDataImported = excelTemplate.ImportCsv(format, csvFile);
}
Assert.False(csvDataImported);
}
private static void CreateXlsxFile(string extractPath, string stringKey)
{
string csvFileName = extractPath + "\\" + "Pd" + stringKey + ".csv";
string excelFileName = extractPath + "\\" + "Pd" + stringKey + ".xlsx";
string worksheetsName = "Pd" + stringKey;
bool firstRowIsHeader = true;
var format = new ExcelTextFormat();
format.Delimiter = ',';
format.EOL = "\n";
using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
package.Save();
}
}
public static void ConvertCSV(string jn, string dir)
{
var format = new ExcelTextFormat();
format.TextQualifier = '"';
format.Delimiter = ',';
format.Encoding = new UTF8Encoding();
format.DataTypes = new eDataTypes[] { eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String,
eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String,
eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String, eDataTypes.String,
eDataTypes.String, eDataTypes.String, eDataTypes.String };
FileInfo file = new FileInfo(dir + "\\" + jn + ".csv");
using (var p = new ExcelPackage())
{
var worksheet = p.Workbook.Worksheets.Add("Holding");
worksheet.Name = "Test";
//Add the sheet to the workbook
if (UrisGroup.tc == "OneCall")
{
worksheet.Name = "One Call Fulfillment Template -";
}
else
{
worksheet.Name = "AG Tab";
// worksheet = p.Workbook.Worksheets.Add("AG Tab");
}
//change format to text
worksheet.Cells.Style.Numberformat.Format = "@";
worksheet.Cells["A1"].LoadFromText(file, format);
//Save the new workbook. We haven't specified the filename so use the Save as method.
p.SaveAs(new FileInfo(dir + @"\" + jn + ".xls"));
}
}
