Hi @Raki ,
First, you can use OleDb to convert Excel data into DataTable data.
Code:
public static System.Data.DataTable DBExcelToDataTable(string pathName, string sheetName = "")
{
System.Data.DataTable dt = new System.Data.DataTable();
string ConnectionString = string.Empty;
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("The file does not exist"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
case ".xlsx":
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
break;
default:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
try
{
con.Open();
if (sheetName != "")
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
try
{
apt.Fill(dt);
}
catch (Exception ex) { throw new Exception("The specified worksheet name was not found in the Excel file," + ex.Message); }
dt.TableName = sheetName;
}
else
{
var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { });
if (tables.Rows.Count == 0)
{ throw new Exception("Excel must contain a table"); }
foreach (System.Data.DataRow row in tables.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strSheetTableName, null });
if (tableColumns.Rows.Count < 2)
continue;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
apt.Fill(dt);
dt.TableName = strSheetTableName.Replace("$", "").Replace("'", "");
break;
}
}
}
if (dt.Rows.Count < 2)
throw new Exception("The table must contain data");
System.Data.DataRow headRow = dt.Rows[0];
foreach (System.Data.DataColumn c in dt.Columns)
{
string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();
if (headValue.Length == 0)
{ throw new Exception("You must enter column headings"); }
if (dt.Columns.Contains(headValue))
{ throw new Exception("You cannot use duplicate column headers:" + headValue); }
c.ColumnName = headValue;
}
dt.Rows.RemoveAt(0);
return dt;
}
catch (Exception ee)
{ throw ee; }
finally
{ con.Close(); }
}
Since the time date is fixed in the Excel standard column, you can filter the date by a fixed column in DataTable, and then get the match by iterating through the data within all these dates.
Finally use Conholdate.Total for . .NET completes the conversion of Excel to PDF.
Code:
using (Converter converter = new Converter("input.xlsx"))
{
WatermarkOptions watermark = new WatermarkOptions
{
Text = "Sample watermark",
Color = Color.Red,
Width = 100,
Height = 100,
Background = true
};
PdfConvertOptions options = new PdfConvertOptions
{
Watermark = watermark
};
converter.Convert("output.pdf", options);
}
Best regards,
Qi You
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.