我正在嘗試使用以下代碼根據起始地址和范圍從 Excel 作業表中獲取最后一個單元格地址以進行合并。
我有像這樣的起始單元格地址，X并希望使用給定的范圍獲取結束單元格地址。例如，起始地址為X，范圍為 7，則結束單元格地址為AD。
我已嘗試使用以下方法，但得到錯誤的終端單元地址
private static readonly char[] BaseChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
private static readonly Dictionary<char, int> CharValues = BaseChars
.Select((c, i) => new { Char = c, Index = i })
.ToDictionary(c => c.Char, c => c.Index);
public static string IntToBase(int value)
{
int targetBase = BaseChars.Length;
// Determine exact number of characters to use.
char[] buffer = new char[Math.Max(
(int)Math.Ceiling(Math.Log(value 1, targetBase)), 1)];
var i = buffer.Length;
do
{
buffer[--i] = BaseChars[value % targetBase];
value /= targetBase;
}
while (value > 0);
return new string(buffer, i, buffer.Length - i);
}
public static int BaseToInt(string number)
{
_ = number ?? throw new ArgumentNullException(nameof(number));
char[] chrs = number.ToCharArray();
int m = chrs.Length - 1;
int n = BaseChars.Length, x;
int result = 0;
foreach (char c in chrs)
{
x = CharValues[c];
result = x * (int)Math.Pow(n, m--);
}
return result;
}
public static string GetLastCellAddress(string number, int cellCount)
{
int startVal = BaseToInt(number);
return Enumerable.Range(startVal, cellCount).Select(i => IntToBase(i)).Last();
}
我正在使用上述功能，如下所示
var environmentsLastCellAddress = ExcelBuilderExtensions.GetLastCellAddress(startColumnAddress, spaceTypeLibraryByPropertiesCount);
如果我給出了像這樣的起始單元格地址X并且計數是7，上面的函式給出了錯誤的結束地址，我應該得到結束單元格地址AD而不是我得到的地址BD.
任何人都可以讓我知道上面的代碼有什么問題嗎？那將非常感謝我。提前謝謝了！！
uj5u.com熱心網友回復：
我希望以下內容對您有用。
int以string參考...
將 Excel 列號轉換為列名或字母：
string以int參考...
使用 Interop 從 Excel 檔案中洗掉空行和列的最快方法
static void Main(string[] args) {
Console.WriteLine("Start: A 0: " GetAddedRange("A", 0));
Console.WriteLine("Start: A 1: " GetAddedRange("A", 1));
Console.WriteLine("Start: H 4: " GetAddedRange("H", 4));
Console.WriteLine("Start: AA 26: " GetAddedRange("AA", 26));
Console.WriteLine("Start: BA 11: " GetAddedRange("BA", 11));
Console.WriteLine("Start: CAA 11: " GetAddedRange("CAA", 11));
Console.WriteLine("Start: GAA 11: " GetAddedRange("GAA", 11));
Console.WriteLine("Start: Z 11: " GetAddedRange("Z", 11));
Console.WriteLine("Start: Z - 10: " GetAddedRange("Z", -10));
Console.ReadKey();
}
private static string ColumnIndexToColumnLetter(int colIndex) {
int div = colIndex;
string colLetter = String.Empty;
int mod = 0;
while (div > 0) {
mod = (div - 1) % 26;
colLetter = (char)(65 mod) colLetter;
div = (int)((div - mod) / 26);
}
return colLetter;
}
private static int ParseColHeaderToIndex(string colAdress) {
int[] digits = new int[colAdress.Length];
for (int i = 0; i < colAdress.Length; i) {
digits[i] = Convert.ToInt32(colAdress[i]) - 64;
}
int mul = 1;
int res = 0;
for (int pos = digits.Length - 1; pos >= 0; --pos) {
res = digits[pos] * mul;
mul *= 26;
}
return res;
}
private static string GetAddedRange(string startCol, int addedRange) {
int startingCol = ParseColHeaderToIndex(startCol);
return ColumnIndexToColumnLetter(startingCol addedRange);
}
同樣從您的倒數第二個評論……X 7 將是 AE……而不是 AD。
uj5u.com熱心網友回復：
如果您使用 excel，您也可以讓 excel 來完成這項作業：
Private Function GetColName(ColNr As Integer) As String
Dim parts() As String
parts = Split(Cells(1, ColNr).Address, "$")
GetColName = parts(1)
End Function
Private Function GetColNr(ColName As String) As Long
GetColNr = Range(ColName "1").Column
End Function
Sub test()
MsgBox GetColName(GetColNr("X") 7) 'will post AE
End Sub
您可以將其組合到一個函式中并添加錯誤處理。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/360976.html