Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Jul 20, 2020 10:05 am

We have a standard template file which has 3 worksheets 1. Instructions 2. Lookups 3. Main data sheet. I have some validations written in xlsb file.

The Lookup sheet will have values that will be having values that needs to be populated in Main data sheet

The Main data sheet will have values from DB and also user will be able to add new data.

I am attaching a sample template file.

Please can you provide code to write data into the attached xlsb file (VLookUp sheet and KR Mapping sheet)
Attachments
KRMapping_Template_AO.zip
Sample File
(55.64 KiB) Downloaded 154 times

vmuralia
 
Posts: 1
Joined: Mon Jul 20, 2020 7:18 am

Tue Jul 21, 2020 10:23 am

Hello,

Thanks for your message.
Please download our latest Spire.XLS Pack(Hotfix) Version:10.7.2 and refer to the following sample code to write the dataTable data to your excel file, if there is any other question, please share us with your data and your desired effect of output to help further look into it.
Code: Select all
//from dataBase.mdb/Customer table to dataTable
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""yourDataBase.mdb"";User Id=;Password=";
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from Customer";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
dataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
//write data to Excel sheet
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"KRMapping_Template_AO.xlsb");
//get the second sheet
Worksheet sheet = workbook.Worksheets[1];
sheet.InsertDataTable(dataTable, true, 1, 1);
//save
string result = @"output.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2013);

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Return to Spire.XLS

cron