I have a code that would take an excel sheet and convert it to google sheet. It is working but only copying the values.

How can I copy the styling and range (merged cells) also to google Sheets?

Is there also a more simple way to just simulate as I am opening the file as is on google sheets?

        foreach (var sheet in wb.Worksheets)
        {
            if (sheet.Index == 0)
            {
                // First sheet is created by default, so only set range
                range = $"{defaultWorksheetName}!A:Y";
            }
            else
            {
                // Add a new sheet
                AddSheet(sheetService, spreadhsheet.SpreadsheetId, sheet.Name);
                range = $"{sheet.Name}!A:Y";
            }

            // Get number of rows and columns
            int rows = sheet.Cells.MaxDataRow;
            int cols = sheet.Cells.MaxDataColumn;

            IList<IList<Object>> list = new List<IList<Object>>() { };

            // Loop through rows
            for (int i = 0; i < rows; i  )
            {
                List<object> lists = new List<object>();

                // Loop through each column in selected row
                for (int j = 0; j < cols; j  )
                {
                    lists.Add(sheet.Cells[i, j].Value);
                }
                list.Add(lists);
            }

            // Define range
            ValueRange VRange = new ValueRange();
            VRange.Range = range;

            // Set values
            VRange.Values = list;

            // Create request
            SpreadsheetsResource.ValuesResource.UpdateRequest upd = sheetService.Spreadsheets.Values.Update(VRange, spreadhsheet.SpreadsheetId, range);
            upd.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

            // Execute request
            UpdateValuesResponse response = upd.Execute();

            // Get response
            string responseString = JsonConvert.SerializeObject(response);

        }

CodePudding user response：

It appears that you are looping through the Excel rows to create an object to build the Google Sheet manually. Looking at the Microsoft documentation, you probably could also read the formatting for these cells and use the Sheets API to apply it, however, the process seems like it could be too time-consuming and error-prone.

My recommendation would be to just have Google do the work for you. The Drive API is capable of uploading Excel files and converting them to Google Sheets by setting the MimeType to application/vnd.google-apps.spreadsheet. There are some limitations, but generally it does a good job of keeping the cell formatting the same.

Google's documentation doesn't include .NET samples and I'm no expert in it, but you can check out their .NET API which does have the Files.create methods, or you could use their REST API instead.

Sources:

• Importing files to Google Docs types
• Drive API .NET documentation
• Drive REST documentation

CodePudding user response：

I found that is much better and easier to use Drive API instead of Sheets API and upload the file as is, then get the link and it will open it in Google sheets.

public static string UploadFile(Stream file, string fileName, string fileMime, string fileDescription)
    {
        DriveService service = GetService();


        var driveFile = new Google.Apis.Drive.v3.Data.File();
        driveFile.Name = fileName;
        driveFile.Description = fileDescription;
        driveFile.MimeType = "application/vnd.google-apps.spreadsheet";

        var request = service.Files.Create(driveFile, file, driveFile.MimeType);
        request.Fields = "id, webViewLink";

        var response = request.Upload();
        if (response.Status != Google.Apis.Upload.UploadStatus.Completed)
            throw response.Exception;

        return request.ResponseBody.WebViewLink;
    }

private static DriveService GetService()
    {
        string[] Scopes = { SheetsService.Scope.Drive };
        string ApplicationName = "Excel to Google Sheet";

        UserCredential credential = null;

        using (var stream =
            new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
        {
            string credPath = "token.json";

            var thread = new Thread(() =>
                                    credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                GoogleClientSecrets.FromStream(stream).Secrets,
                Scopes,
                "user",
                CancellationToken.None,
                new FileDataStore(credPath, true)).Result)
        
            { IsBackground = false };

            thread.Start();
            if (!thread.Join(20000))
            {
                throw new Exception("Timeout exception..!!!");
            }
            else
            {
                var service = new DriveService(new BaseClientService.Initializer()
                {
                    HttpClientInitializer = credential,
                    ApplicationName = ApplicationName,
                });

                return service;
            }

        }