C# Excel XLSX를 사용해 숫자 서식 지정을 프로그래밍 방식으로 사용자 정의하는 방법
페이지 정보
작성자 GrapeCity
작성일 2023-09-06 15:22 조회 515회
댓글 0건
본문
첨부파일
관련링크
숫자 서식(Number Format)은 가독성 향상, 데이터 일관성, 쉬운 수식 계산/분석 및 빠른 의사 결정을 위해 Microsoft Excel에서 제공하는 중요하고 자주 사용되는 기능입니다.
MS Excel에도 데이터가 수동 입력되거나, 소스에서 채워지거나, HTML 테이블과 같은 다른 위치에서 복사될 때 관련 데이터 서식을 지능적으로 적용하는 기능이 있습니다.
하지만 적용된 서식에 따라 데이터의 목적이나 특성이 바뀌는 경우가 있습니다.
예를 들면, 다음과 같은 일이 발생합니다.
일부 지역별 서식으로 저장된 숫자는 일반 서식으로 저장되어 수식에서 올바르게 평가되지 않습니다.
앞에 0이 있는 식별자는 숫자 서식으로 변환되어 0이 사라집니다.
날짜는 해당하는 숫자로 변환됩니다.
위와 같은 상황에서는 데이터에 적합하고 관련성 높은 숫자 서식을 수동으로 설정해야 합니다.
이번 블로그에서는 Excel 파일에서 숫자 서식 지정이 필요한 일반적인 데이터 시나리오와 .NET용 GcExcel API를 사용해 적용하는 방법을 살펴보겠습니다.
GcExcel의 NumberFormat 샘플을 다운로드하여 함께 따라와 주시길 바랍니다.
GcExcel의 NumberFormat 속성
GcExcel은 차트 제목, 데이터라벨(DataLabels), 셀/범위, 텍스트 스타일, 피벗 필드 등과 같은 모든 텍스트 개체에 대해 NumberFormat 속성을 지원합니다.
다음은 문자열을 허용하는 구문입니다.
<format for positive number>;<format for negative number>;<format for zero>;<format for text>
가장 일반적인 시나리오에서 서식 코드는 "일반" 또는 "##.00"과 같은 구문의 한 섹션이 포함되며 서식은 양수, 음수, 0, 텍스트 등 모든 데이터 유형에 적용됩니다.
하지만 여러 데이터 시나리오에서는 아래에 제시된 항목에서 다루는 것처럼 세미콜론(;)으로 구분된 구문의 여러 섹션을 사용합니다.
텍스트로 저장된 날짜를 날짜 서식으로 가져오기
엔터프라이즈 응용 프로그램에서 생성된 숫자 또는 날짜 유형 데이터는 텍스트로 저장되는 경우가 많습니다.
이런 현상은 주로 JSON이나 CSV로 저장된 데이터에서 발생합니다.
예를 들어 다음과 같습니다.
[
. . .
{
"Area": "North America",
"City": "Chicago",
"Category": "Consumer Electronics",
"Name": "Bose 785593-0050",
"Revenue": 92800,
"SalesFrom": "1/10/2022",
"SalesTo": "1/11/2022"
},
. . .
]상단과 같은 데이터가 통합문서의 셀에 로드되면 일반 숫자 서식으로 저장됩니다.
생성된 Excel 스프레드시트에 이를 날짜로 저장하려면 아래 코드와 같이 데이터를 가져온 후, 숫자 서식을 각각의 데이터 열에 적용합니다.
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.AutoParse = true;
// Get data from json file.
string jsonText = string.Empty;
using (Stream stream = GetResourceStream("json\\DataBinding.json"))
using (StreamReader reader = new StreamReader(stream))
{
jsonText = reader.ReadToEnd();
}
IWorksheet worksheet = workbook.ActiveSheet;
worksheet.DataSource = new JsonDataSource(jsonText);
worksheet.Columns[5].NumberFormat = "dd/mm/yyyy";
worksheet.Columns[6].NumberFormat = "d/m/yy";
// Save to an excel file
workbook.Save("JsonSource.xlsx");출력 결과는 아래 이미지에서 볼 수 있습니다.
공학용 서식이 아닌 다른 서식으로 큰 숫자 표시
Excel은 대부분 UPC/GTIN 코드, ID 등 자릿수가 12를 넘는 수의 경우, 숫자 서식이 아닌 공학용 서식으로 표시할 가능성이 높습니다.
정확성을 유지하기 위해 15자리 정밀도 제한이 구현되어 있기 때문입니다.
큰 수를 공학용 표기 방식에서 숫자 표기로 바꾸려면 아래 코드에 보이는 대로 숫자 코드를 사용합니다.
IWorkbook workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Worksheets.Add().Name = "Big Number"; workbook.Worksheets["Big Number"].Range["A1"].Value = "From Data"; workbook.Worksheets["Big Number"].Range["B1"].Value = "Converted to number"; workbook.Worksheets["Big Number"].Range["A2:B2"].Value = 6589423145697; workbook.Worksheets["Big Number"].Range["B2"].NumberFormat = "0";
다음 이미지는 숫자 서식이 적용된 큰 수와 적용되지 않은 큰 수를 보여 줍니다.
10진수에 앞 또는 뒤 0 추가
Excel에서는 00123, 1234.5600 등과 같이 0으로 시작하거나 끝나는 우편 번호, 시스템 생성 식별자와 같은 엔터티를 표시하는 숫자가 앞 또는 뒤의 0이 제거되어 자동으로 숫자로 변환됩니다.
0을 유지하려면 아래 코드 조각 보이는 대로 자리 표시자 '0'을 사용해 숫자 서식을 지정하세요.
workbook.Worksheets.Add().Name = "Zeros"; workbook.Worksheets["Big Number"].Range["A1"].Value = "From Data"; workbook.Worksheets["Big Number"].Range["B1"].Value = "Converted to number"; workbook.Worksheets["Big Number"].Range["A2"].Value = 00123; workbook.Worksheets["Big Number"].Range["A2"].NumberFormat = "00000"; workbook.Worksheets["Big Number"].Range["B2"].Value = 1234.5600; workbook.Worksheets["Big Number"].Range["B2"].NumberFormat = "#.0000";
아래 이미지에 표시된 대로 숫자는 숫자 서식이 적용되거나 적용되지 않은 상태로 표시됩니다.
조건에 따른 숫자/텍스트 색 지정
데이터 집합에 양수, 음수, 텍스트 콘텐츠가 혼합되어 있다고 가정해 보겠습니다.
(예: 긍정 혹은 부정 순위 평가와 코멘트가 포함된 설문 조사 결과)
조건부 서식 외에도 숫자 서식을 사용해 데이터를 강조 표시하여 가독성을 높일 수 있습니다.
숫자 서식은 8가지 기본색(검은색, 초록색, 빨간색, 하얀색, 파란색, 자홍색, 노란색, 청록색)을 지원합니다.
아래 코드 섹션에 표시된 대로 각 숫자의 대괄호 [ ] 안에 색상을 추가하기만 하면 됩니다.
object[,] data = new object[,]{
{"Name", "City", "Result"},
{"Richard", "New York", 10 },
{"Nia", "New York", 5},
{"Jared", "New York",-6 },
{"Natalie", "Washington",-2 },
{"Damon", "Washington", "Unimpressive" },
{"Angela", "Washington", "Unimpressive" }
};
workbook.Worksheets.Add().Name = "Conditional Format";
workbook.Worksheets["Conditional Format"].Range["A1:C7"].Value = data;
workbook.Worksheets["Conditional Format"].Range["C2:C7"].NumberFormat = "General;[Red]-General;;[Blue]General";
workbook.Worksheets["Conditional Format"].Columns.AutoFit();아래 이미지에서 결과(Result) 열을 확인해 보세요.
숫자를 텍스트 형식으로 표시
시험 점수, Boolean flags(부울 플래그: 상태를 기록하고 처리 흐름을 제어하기 위한 변수), 나이 같은 데이터는 불연속적인 경우가 많습니다.
분석 목적을 위해 해당 숫자를 합격/불합격, 참/거짓 같이 의미를 가지거나, 분류 가능하거나, 읽을 수 있는 텍스트로 교체해야 합니다.
조건부 서식, 수식 및 함수 외에도 아래와 같이 대괄호 [ ] 안에 원하는 조건을 추가합니다.
요구 사항에 맞게 숫자 형식을 사용할 수 있습니다.
object[,] data1 = new object[,]{
{"Name", "Subject", "Result"},
{"Richard", "English", 70 },
{"Richard", "Elective-I", 65},
{"Richard", "Science",45 },
{"Richard", "Mathematics",56 },
{"Richard", "Elective-II", 25 },
{"Richard", "Language", 38 },
{"Natalie", "English", 30 },
{"Natalie", "Elective-I", 45},
{"Natalie", "Science",53 },
{"Natalie", "Mathematics",28 },
{"Natalie", "Elective-II", 32 },
{"Natalie", "Language", 69 }
};
workbook.Worksheets.Add().Name = "DisguiseNumber";
workbook.Worksheets["DisguiseNumber"].Range["A1:C13"].Value = data1;
//disguise number without color code
workbook.Worksheets["DisguiseNumber"].Range["C2:C13"].NumberFormat = "[<35]\"Fail\";\"Pass\"";
//disguise number with color code
//workbook.Worksheets["DisguiseNumber"].Range["C2:C13"].NumberFormat = "[<35][Red]\"Fail\";[Green]\"Pass\"";
workbook.Worksheets["DisguiseNumber"].Columns.AutoFit();아래 이미지는 C열의 원본 데이터와 D열에 있는 텍스트 형식으로 표시된 데이터를 보여 줍니다.
천(K), 백만(M) 또는 십억(B)으로 숫자 서식 지정
큰 숫자, 특히 금액/가격 또는 인구와 같은 수치를 다루는 경우 보통 천, 백만 등 보다 더 짧은 단위로 표시합니다.
아래 코드와 같이 숫자 서식을 사용해 여러 숫자 단위로 큰 수의 서식을 지정할 수 있습니다.
workbook.Worksheets.Add().Name = "NumberScale"; workbook.Worksheets["NumberScale"].Range["A1"].Value = "Number scale Format"; workbook.Worksheets["NumberScale"].Range["A1:D1"].MergeCells = true; workbook.Worksheets["NumberScale"].Range["A3"].Value = "Number"; workbook.Worksheets["NumberScale"].Range["B3"].Value = "Thousands"; workbook.Worksheets["NumberScale"].Range["C3"].Value = "Million"; workbook.Worksheets["NumberScale"].Range["D3"].Value = "Billion"; workbook.Worksheets["NumberScale"].Range["A4:D4"].Value = 456321456; workbook.Worksheets["NumberScale"].Range["B4"].NumberFormat = "0.00, \"K\""; workbook.Worksheets["NumberScale"].Range["C4"].NumberFormat = "$0.00,, \"M\""; workbook.Worksheets["NumberScale"].Range["D4"].NumberFormat = "$#.##,,, \"B\"";
하단의 이미지는 여러 단위로 표시된 숫자를 보여 줍니다.
셀 값 숨기기
셀 값 숨기기는 숫자 서식과 관련해 가장 단순한 시나리오입니다.
하지만 셀에서 숨겨진 계산을 수행하고 어떠한 값도 표시하지 않는 경우, 아주 유용할 수 있습니다.
셀에 값 없이 아이콘 집합을 표시하는 경우를 예로 들어 보겠습니다.
Excel에 투명 색 설정이 없으므로 아래와 같이 숫자 서식을 사용하여 셀 내용을 숨길 수 있습니다.
workbook.Worksheets[0].Range["A1"].NumberFormat = ";;;;" ;
시스템 로케일(Locale: 각 국가의 고유한 언어) 이외의 서식으로 데이터 표시
클라우드 시스템과 글로벌 비즈니스의 경우 특정 지역에서 데이터를 사용하지 못할 수 있습니다.
데이터, 특히 숫자 및 날짜는 텍스트로 저장 및 표시되거나 Excel로 변환됩니다.
특정 형식으로 표시하려면 숫자 서식에서 로케일(Locale: 각 국가의 고유한 언어)을 명시적으로 추가하세요.
GcExcel을 사용해 문화별 데이터를 작업하려면 아래에 제시된 세 단계가 필요합니다.
통합 문서에 대한 문화 정보 설정
숫자/날짜 구문 분석
숫자 서식 적용
workbook.Worksheets.Add().Name = "Culture";
workbook.Worksheets["Culture"].Range["A1"].Value = "Date as Text";
workbook.Worksheets["Culture"].Range["A2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["A3"].Value = "08/07/2022";
workbook.Culture = CultureInfo.GetCultureInfo("en-US"); //set culture of data available
workbook.AutoParse = true;
workbook.Worksheets["Culture"].Range["B1"].Value = "Date converted to system locale (not US)";
workbook.Worksheets["Culture"].Range["B2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["B3"].Value = "08/07/2022";
workbook.Worksheets["Culture"].Range["C1"].Value = "Formatted to US date format";
workbook.Worksheets["Culture"].Range["C2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["C3"].Value = "08/07/2022";
workbook.Worksheets["Culture"].Range["C2:C3"].NumberFormat = "[$-en-US]mm-dd-yyyy";
workbook.Worksheets["Culture"].Columns.AutoFit();
문화별 데이터에 숫자 서식을 적용하려면 를 확인해 보시길 바랍니다.
10진수를 분수로 변환
분수는 10진수를 표시하는 특별한 방식입니다.
10진수를 분수로 표시하려면 숫자 서식 코드에 슬래시(/)를 포함하고, 정수 부분을 공백으로 분리합니다.
예를 들어 다음과 같습니다.
- #/# 또는 # ##/## : 표시할 분수의 나머지 자릿수에 따라 달라집니다.
###/### : 최대 3자리의 가분수를 표시합니다.
##/N : 밑수를 사용하여 분수를 특정 분모로 반올림합니다.
아래 코드의 경우, 10진수가 다음 이미지에 보이는 대로 표시됩니다.
workbook.Worksheets.Add().Name = "Fractions"; workbook.Worksheets["Fractions"].Range["A1"].Value = "Decimal"; workbook.Worksheets["Fractions"].Range["B1"].Value = "# ##/##"; workbook.Worksheets["Fractions"].Range["C1"].Value = "###/###"; workbook.Worksheets["Fractions"].Range["D1"].Value = "##/4"; workbook.Worksheets["Fractions"].Range["A2:D2"].Value = 0.25; workbook.Worksheets["Fractions"].Range["A3:D3"].Value = 5.64; workbook.Worksheets["Fractions"].Range["A4:D4"].Value = 2.5; workbook.Worksheets["Fractions"].Range["B2:B4"].NumberFormat = "# ##/##"; workbook.Worksheets["Fractions"].Range["C2:C4"].NumberFormat = "###/###"; workbook.Worksheets["Fractions"].Range["D2:D4"].NumberFormat = "##/4"; workbook.Worksheets["Fractions"].Columns.AutoFit();
결론
GcExcel은 Microsoft Excel의 숫자 서식을 따릅니다.
링크된 소스에서 및 에 대해 자세히 알아보시길 바랍니다.
지금 바로 GcExcel .NET을 다운로드하여 직접 테스트해 보세요!
댓글목록
등록된 댓글이 없습니다.
