Write a C program that provides a simple spreadsheet. The program should allow the user to enter numeric values, text, or formulas into any cell. As new data is entered, the spreadsheet should be updated and redisplayed. The program should run until the user quits.
The maximum size of the spreadsheet should be 9 x 9 cells. Rows should be labeled with numbers, starting from 1, and columns should be labeled with letters, starting from A. Cells should be referenced by a letter number pair, with no space or symbol between; for example, E9. Lowercase and uppercase letters to reference cells are allowed and must be recognized.
The program cannot prompt what type of entry is being made into a cell. It should prompt only for an input. If the user types data consisting only of numeric values, for example 4.53, then the program must recognize that input as a number. All numbers should be considered real numbers. If the user types input describing a formula (discussed below), then the program must recognize that input as a formula. Everything else defaults to text. The display of the spreadsheet should be aligned in an easy-to-read 9 x 9 grid. If text entries or other displayed content go beyond the given display size, the display should be truncated. When entering a new input for a cell, the program must display the old (previous) input.
You should include formulas in the program. Formulas are of the form =AVERAGE(A2,A5). This formula would return the average value from the four cells A2, A3, A4, and A5.
Three formulas are required: AVERAGE, SUM, and RANGE.
RANGE should return the difference between the largest and smallest value in the input.
SUM should return the cumulative total. The input must be a horizontal or vertical 1D range of cells, described by the end points. The formula should compute a result using only values within the given range of cells; it should ignore empty cells and those containing text or another formula. If there are no values within the given range of cells, the result should default to zero.
Code:
#include<stdio.h>
#include<stdlib.h>
#include<limits.h>
#include<string.h>
int isNumber(char str[20])
{
int i,j,k,flag=1;
for(i=0;str[i]!='\0';i++)
{
if((str[i]>='0'&&str[i]<='9')||(str[i]=='.'))
{}
else
{
flag=0;
break;
}
}
return flag;
}
int main()
{
int t,i1,j1,i2,j2,k,l,m,n,row,col,i,j;
double spreadsheet[10][10]={0}; // matrix to store the Integer values entered by the user and the values computed as result of formula
char sheet[10][10][20]={0}; // The actual spreadsheet which stores the content to be displayed
int flag[10][10]={0}; // Flag matrix to check whether cell contains value entered by the user or the one computed by the formula
char input[20]; // Input value received from user
char cellnum[4]; // Cell number received from the user
char *sub;
for(i=0;i<=9;i++) // Initialization of the sheet matrix to contain default values
for(j=0;j<=9;j++)
{
if(i==0&&j!=0)
sheet[i][j][0]='A'+j-1;
else
if(i!=0 && j==0)
sheet[i][j][0] = '0'+i;
else
if(i==0&&j==0)
sheet[i][j][0]='\0';
else
sheet[i][j][0]='_';
}
while(1) // Infinite loop to take input till user quits
{
double sum=0,avg=0;
int maxm=INT_MIN,minm = INT_MAX,cnt =0;
printf("\nEnter the cell number : ");
scanf("%s",cellnum);
if(cellnum[0]>='A' && cellnum[0]<='Z')
col = cellnum[0]-'A'+1;
else
col = cellnum[0] - 'a'+1; // Extracting the row and column number from cell number
row = cellnum[1] - '1'+1;
if(row>9 || row<0 || col>9 || col<0)
{
printf("\n Invalid cell number \n");
continue;
}
printf("\nEnter the value to be inserted : ");
scanf("%s",input);
if(strstr(input,"AVERAGE")!= NULL ) // checking if user entered formuula : AVERAGE( , )
{
sub = strstr(input,"(");
if(sub ==NULL)
printf("Wrong input");
else
{
if(*(sub+1) >= 'A' && *(sub+1) <= 'Z')
j1= *(sub+1)-'A'+1;
else
j1= *(sub+1) - 'a'+1;
i1 = *(sub+2)-'1'+1;
sub=sub+4;
if(*sub >= 'A'&& *sub <='Z')
j2= *sub - 'A'+1;
else
j2= *sub - 'a'+1;
i2 = *(sub+1)-'1'+1;
if(i2 == i1)
{
for(j=j1;j<=j2;j++)
{
if(flag[i1][j] == 1)
{
avg+=spreadsheet[i1][j];
cnt++;
}
}
}
if(j2 == j1)
{
for(i=i1;i<=i2;i++)
{
if(flag[i][j1] == 1)
{
avg+=spreadsheet[i][j1];
cnt++;
}
}
}
spreadsheet[row][col] = avg/cnt;
//strcpy(sheet[row][col],ftoa(spreadsheet[row][col]));
sprintf(sheet[row][col],"%.2f",spreadsheet[row][col]); // Inserting the result into the cell
if(strlen(sheet[row][col])>6)
{
sheet[row][col][6]='.';
sheet[row][col][7]='.';
sheet[row][col][8]='\0';
}
}
}
else
if(strstr(input,"SUM") != NULL) // Checking if the user entered formula : SUM( , )
{
sub = strstr(input,"(");
if(sub ==NULL)
printf("Wrong input");
else
{
if(*(sub+1) >= 'A' && *(sub+1) <= 'Z')
j1= *(sub+1)-'A'+1;
else
j1= *(sub+1) - 'a'+1;
i1 = *(sub+2)-'1'+1;
sub=sub+4;
if(*sub >= 'A'&& *sub <='Z')
j2= *sub - 'A'+1;
else
j2= *sub - 'a'+1;
i2 = *(sub+1)-'1'+1;
if(i2 == i1)
{
for(j=j1;j<=j2;j++)
{
if(flag[i1][j] == 1)
{
sum+=spreadsheet[i1][j];
cnt++;
}
}
}
if(j2 == j1)
{
for(i=i1;i<=i2;i++)
{
if(flag[i][j1] == 1)
{
sum+=spreadsheet[i][j1];
cnt++;
}
}
}
spreadsheet[row][col] = sum;
//strcpy(sheet[row][col],ftoa(sum));
sprintf(sheet[row][col],"%.2f",spreadsheet[row][col]); // inserting result into the cell
if(strlen(sheet[row][col])>6)
{
sheet[row][col][6]='.';
sheet[row][col][7]='.';
sheet[row][col][8]='\0';
}
}
}
else
if(strstr(input,"RANGE") != NULL) // Checking if the user enterd formula : RANGE( , )
{
sub = strstr(input,"(");
if(sub ==NULL)
printf("Wrong input");
else
{
if(*(sub+1) >= 'A' && *(sub+1) <= 'Z')
j1= *(sub+1)-'A'+1;
else
j1= *(sub+1) - 'a'+1;
i1 = *(sub+2)-'1'+1;
sub=sub+4;
if(*sub >= 'A'&& *sub <='Z')
j2= *sub - 'A'+1;
else
j2= *sub - 'a'+1;
i2 = *(sub+1)-'1'+1;
if(i1 == i2)
{
for(j=j1;j<=j2;j++)
{
if(flag[i1][j] == 1 && maxm<spreadsheet[i1][j])
maxm = spreadsheet[i1][j];
if(flag[i1][j] == 1 && minm > spreadsheet[i1][j])
minm = spreadsheet[i1][j];
}
}
if(j1 == j2)
{
for(i=i1;i<=i2;i++)
{
if(flag[i][j1] == 1 && maxm < spreadsheet[i][j1])
maxm = spreadsheet[i][j1];
if(flag[i][j1] == 1 && minm > spreadsheet[i][j1])
minm = spreadsheet[i][j1];
}
}
spreadsheet[row][col] = maxm-minm;
//strcpy(sheet[row][col],ftoa(spreadsheet[row][col]));
sprintf(sheet[row][col],"%.2f",spreadsheet[row][col]); // Inserting the result into the cell
if(strlen(sheet[row][col])>6)
{
sheet[row][col][6]='.';
sheet[row][col][7]='.';
sheet[row][col][8]='\0';
}
}
}
else if(isNumber(input)) // Checking if the value entered by the user is a number
{
spreadsheet[row][col] = atoi(input);
flag[row][col] = 1;
strcpy(sheet[row][col],input);
if(strlen(sheet[row][col])>6)
{
sheet[row][col][6]='.';
sheet[row][col][7]='.';
sheet[row][col][8]='\0';
}
}
else
{
strcpy(sheet[row][col],input); // If value entered is text , simply insert the text into the cell
if(strlen(sheet[row][col])>6)
{
sheet[row][col][6]='.';
sheet[row][col][7]='.';
sheet[row][col][8]='\0';
}
}
for(i=0;i<=9;i++)
{
for(j=0;j<=9;j++)
printf("%8s ",sheet[i][j]);
printf("\n");
}
printf("\n\n Pervious input : Cell Number : %s , Value : %s\n\n",cellnum,input);
}
return 0;
}